SUMIFS to iterate through a table and only sum totals if one of the cells in a row are empty?

Question

Level 1

4 points

SUMIFS to iterate through a table and only sum totals if one of the cells in a row are empty?

I have a Numbers spreadsheet that I use to track my stock trading. I have multiple accounts. My main table ("My Portfolio" in the Portfolio screenshot below) shows the usual trading info:

Since my accounts are listed only as "numbers" and selected from a popup menu, I have a second smaller table (Account Values below) that ties the account numbers to a more readable account name and I'm now adding a column to sum up the current market value of the stocks in each account. I plan to cross-reference these calculated values to my broker accounts to verify there are no errors. Populating the "Current Value" field is where I'm stuck.

The goal is to sum the market value of the various stocks (My Portfolio::Value column) in each account (My Portfolio::Account, number 230 in example above). At the same time, I also want to iterate through the Sell Date column to exclude those rows which already have data in them; this will exclude stocks from Current Value calculations that I've already sold since they will include a Sell Date. I want to then populate the Current Value field for each account in the Account Values table. When working properly, the formula's result should (closely) mirror what I see from my broker website.

Here is the formula I'm currently using in the Current Value field for account 230:

SUMIFS(My Portfolio::Value,My Portfolio::Account,230,My Portfolio::Sell Date,,)

As you can see, the final condition to check if I've sold the stock (Sell Date) is currently empty. I can't figure a way to write the condition such that it doesn't generate an error. How do I iterate through the Sell Date column to only sum those rows that have no data in the Sell Date cell? I've looked at ISBLANK but it's not useable because it only evaluates a single cell; I can't specify a range of cells to check because the number of stocks I'm holding and the number I've already sold are always changing.

Surely there is a guru out there who can help me? Thanks so much for any input you can share.

Kevin

Posted on Jan 28, 2025 3:43 PM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

21,939 points

Jan 28, 2025 9:37 PM in response to CajunKev

Hi Kev,

Use "" (NULL, blank) in your formula.

I am using Column Row references. This will work just as well with 'Use Header Names as Labels' (Menu > Numbers > Settings > General).

Formula in C2 is SUMIFS(My Portfolio::J,My Portfolio::G,$A2,My Portfolio::K,"")

Regards,

Ian.

Reply

Answer 2

CajunKev Author

Level 1

4 points

Jan 30, 2025 10:34 AM in response to Yellowbox

Ian, thanks so much for replying. I did try "" multiple times before I posted.

Alright, I feel pretty dumb now. I was planting the formula in C2 in as you were, and had even tried the same formula as you, and the formula was actually working every time it generated a $0 amount. The problem was that account 230 didn't have any active trades in it, so $0 was actually the correct answer for that account. When I moved the formula to 233, which did have trades in the account, it worked fine.

I do deeply appreciate your time in seeing me through this, and it was only in looking at your working example, that I was faced with finding another cause - other than the calc - for my problems. So you still helped me, and I thank you for the time it took you to work up that example and post it for me. Best wishes to you my friend!

Thanks again,

Kevin

Reply