Sumif function does not add up cells, even when criteria is blank

[Beckster]

I have a range of cells which retrurn a value if I use the Sum function.
They are all formatted as numbers. However if I use Sumif it always returns
zero, even if I leave the criteria blank to sum all cells.

 

[Bob Phillips]

What do you mean by ...even if I leave the criteria blank to sum all
cells... You cannot leave a SUMIF criteria blank, and if you meant you used
"", that will total all cells with nothing ion, which would sum to 0
anyway.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Beckster]

Sorry Bob I mean I used "" which should sum corresponding blank cells. To
use the actual numbers

=SUMIF(G11:G28,"",K11:K28)

where K11:k28 is a sum_range range of numbers works fine, but if if use a
different sum_range with the same range and criteria I get a zero result!

 

[Bob Phillips]

Are you sure that the different sum range really does contain numbers and is
not formatted as text.Test it with this formula

=SUMPRODUCT(--(G11:G28=""),--(M11:M28))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Beckster]

Hey Bob the were in the correct format but I've just managed to get it to
work! Thanks for your help.

 

[Gord Dibben]

Beckster

Sounds like the numbers are actually "text".

Re-formatting to numbers will not do the trick.

Select an empty cell formatted as General and copy.

Select your range of "numbers" and Paste Special>Add>OK>Esc.

This will force the numbers to be numbers.


Gord Dibben MS Excel MVP