Excel SUMIF function - what condition am I looking for?

January 31, 2012 2:26 PM

ExcelFilter: how do I get SUMIF to work so that if a cell in the reference range is blank, the corresponding cell in the sum range is picked up in the sum?

I can get it to work such that if there is a figure in the reference range N, the corresponding cell in the sum range I is included:

=SUMIF(N:N,"<>",I:I)

but I want to do the opposite. I want to add up the cells for which the corresponding reference cell is blank. Putting in "=0" doesn't work because, well, it doesn't equal any number, it's just empty.

I know I can do a Total-SUMIF to get around this problem, but I'm sure I must be missing something simple here.

posted by sillymama to Computers & Internet (5 answers total)

Best answer: If you just type =SUMIF(N:N,"",I:I) it should work because if there's no criteria Excel will just use equality with the criteria as the condition
posted by musicismath at 2:31 PM on January 31, 2012

Not sure exactly what you are asking, but I think you are looking for some usage of IsNull().

Confirm or deny.
posted by milqman at 2:32 PM on January 31, 2012

Response by poster: OMG, seriously, not two minutes later, I have figured this out, after spending a ridiculous amount of time on it.

The condition is "".

I suck, and now my moron nature is revealed to MeFites in perpetuity.
posted by sillymama at 2:32 PM on January 31, 2012 [1 favorite]

If the cells are blank their sum is zero.

You can use ISBLANK to test for whether a cell is blank.
posted by dfriedman at 2:32 PM on January 31, 2012

Be careful, Excel has about 5 different definitions of null, blank,0 , etc.
posted by Yowser at 5:12 PM on January 31, 2012