Is there a function for SUM(column A IF column B not empty)

Question

Level 1

6 points

Is there a function for SUM(column A IF column B not empty)

Hello esteemed Numbers community.

After searching long and hard for a simple job cost and invoicing system that would fit my needs, I have decided to do it myself in Numbers.

For the most part it is fairly straight forward but I have two questions. THe second one will be in a separate thread so as to keep things organized.

I imagine this first one is a common request. But it is hard to search for because of too many words...

Say I have my invoice amounts in column A. And Column B is Payment received (Y or N). At the bottom of column B, I want to do a total of only the payments received. In other words, something that does Total of all the amounts in column A but only IF column B has a Yes in it.

Thank you very much for any help.

Bo

Aperture 3, OS X Mountain Lion (10.8.2)

Posted on Apr 2, 2014 5:23 PM

Reply

Answer 1

Best reply

Badunit

Level 7

26,000 points

Apr 2, 2014 5:40 PM in response to Bmachine

=SUMIF(B,"Y",A)

or, if you use a checkbox instead of Y/N,

=SUMIF(B,TRUE,A)

Reply

Answer 2

Bmachine Author

Level 1

6 points

Apr 2, 2014 5:43 PM in response to Badunit

Wow. Awesome. Thank you Badunit.

What about this slightly different version: What if I put the date of pmt received in column B?

So in that case I would want something like "IF B is NOT empty" type of thing....

Reply

Answer 3

Badunit

Level 7

26,000 points

Apr 2, 2014 5:48 PM in response to Bmachine

=SUMIF(B,"<>",A)

This will sum the values in A wherever B is not blank. Any character (even a space character that you cannot see), number, date, etc. will be considered non-blank.

Reply

Answer 4

Bmachine Author

Level 1

6 points

Apr 2, 2014 5:57 PM in response to Badunit

That sounds exactly like what I need. However, I am getting an error message from that. My formula is:

=SUMIF(N,”<>”,M)

Reply

Answer 5

Bmachine Author

Level 1

6 points

Apr 2, 2014 6:14 PM in response to Bmachine

The full error message is:

This formula can’t reference its own cell, or depend on another formula that references this cell.

Reply

Answer 6

Bmachine Author

Level 1

6 points

Apr 2, 2014 6:19 PM in response to Bmachine

OK, I was able to solve this one. It looks like in this case, I cannot simply use the name of the entire column (M and N) because it becomes self referential at the expression line. So I trimmed it to:

=SUMIF(N2:N30,"<>",M2:M30)

and that cleared the error message.

Thanks again Badunit!

Reply

Answer 7

Badunit

Level 7

26,000 points

Apr 2, 2014 6:21 PM in response to Bmachine

A formula cannot reference itself. The reference "N" includes that entire column of cells except for the header(s) and footer(s). Your formula is in one of those cells. You can add a footer row to your table and put the formula in it or you can select the exact ranges you want to use inthe formula, something of the form =SUMIF(N2:N20,"<>",M2:M20)

Reply

Answer 8

Apr 2, 2014 6:23 PM in response to Bmachine

Add a Footer Row and put the expression in it.

Jerry

Reply

Answer 9

Bmachine Author

Level 1

6 points

Apr 2, 2014 6:25 PM in response to Jerrold Green1

Great idea. Thank you Jerrold and Badunit!

Reply