how to write 'not equals blank' criteria in SUMIF

D

David

Hi, and TIA for your time.
I've tried SUMIF(A1:C1,"<>"""",A2:C2) where the 2nd argument needs to be
'not equal to blank. Please advise on the correct syntax.
Thanks
 
N

Nick

Enter the criteria <>"" in a cell then reference the cell instead of typing
the criteria directly in the formula

B1 contains <>""

=SUMIF(A1:C1,B1,A2:C2)

Nick
 
H

Harlan Grove

Nick wrote...
Enter the criteria <>"" in a cell then reference the cell instead of typing
the criteria directly in the formula

B1 contains <>""

=SUMIF(A1:C1,B1,A2:C2)
Click to expand...

First off, B1 would be contained in A1:C1, so bad choice of cell to
contain the criteria. Then, that criteria doesn't do what you think it
does - it'll exclude cells evaluating to the string containing two
double quotes. To exclude blank cells, use

=SUMIF(A1:C1,"<>",A2:C2)

Note that this won't exclude columns in which the row 1 cell evaluates
to a zero length string, "". To exclude those as well, easier to use

=SUMPRODUCT(--(A1:C1<>""),A2:C2)
 
N

Nick

OK good spot, the formula should have read =SUMIF(A1:A3,C1,B1:B3)
with the entry <>"" in cell C1.

You are then summing values in B1:B3 where values in A1:A3 are not blank ie
they contain something even if it is a space.

That works.

Nick
 
H

Harlan Grove

Nick said:
OK good spot, the formula should have read =SUMIF(A1:A3,C1,B1:B3)
with the entry <>"" in cell C1.

You are then summing values in B1:B3 where values in A1:A3 are not blank ie
they contain something even if it is a space.

That works.
Click to expand...
....

That does not work, and you seem not to have tested it last time or this
time.

In a new worksheet, leave A1 blank, enter ="" in A2 and ="""""" in A3, and
enter {1;10;100} in B1:B3. Enter <>"" in C1 and your formula in D1. What
does it return? On my system it returns 11, the sum of B1 and B2, which
correspond to the cells A1 and A2, which appear blank/empty.

Change C1 to <> and D1 returns 110, which reflects the nasty truth that
SUMIF doesn't handle zero length strings as most people expect it should.
 
Top