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

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.

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

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" wrote in message
...
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.

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

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" wrote:

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" wrote in message
...
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.

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

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.

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

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

"Bob Phillips" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.

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

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 SpecialAddOKEsc.

This will force the numbers to be numbers.


Gord Dibben MS Excel MVP

On Fri, 13 Jan 2006 01:40:02 -0800, "Beckster"
wrote:

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.