SUMIF with blank cells criteria -- can it be done?

[jasonconlon]

I'm trying to sum a range of cells (C1:C100) where the corresponding cells (A1:A100) are not blank. Unfortunately because of the format for the criteria which requires operators to be noted with quotation marks, I cannot use "" to represent blank cells. I have tried the formula
=SUMIF(A1:A100,"<>0",C1:C100)
but the zero-value is clearly not equal to an empty cell value, as it is not giving the correct answer unless I specifically change those empty cells to actual zeros.

Any ideas?

 

[Aladin Akyurek]

The value of the 0 cells =value(J23) is 0, even though it is blank in the cell.

Does...

=SUMIF(J23:J32,"=",H23:H32)

do what you want?

 

[TT2013]

Yes, it does, it does..

I thought criteria is supposed to a logical expression. How come "=" work when "=0" doesn't for checking whether the cell is 0?

Thanks again, Aladin

 

[Aladin Akyurek]

Yes, it does, it does..

Great.

I thought criteria is supposed to a logical expression. How come "=" work when "=0" doesn't for checking whether the cell is 0?

SumIf does not coerce empty cells into 0's, so a =0 is unusable. Just a "=" test picks out the empty cells (but not non-blanks), and "<>" the non-empty cells.

Thanks again, Aladin

You are welcome. Thanks for providing feedback.