software

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.

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
quot;quot;, 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)

quot;Becksterquot; gt; wrote in message
...
gt; I have a range of cells which retrurn a value if I use the Sum function.
gt; They are all formatted as numbers. However if I use Sumif it always
returns
gt; zero, even if I leave the criteria blank to sum all cells.
Sorry Bob I mean I used quot;quot; which should sum corresponding blank cells. To
use the actual numbers

=SUMIF(G11:G28,quot;quot;,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!quot;Bob Phillipsquot; wrote:

gt; What do you mean by ...even if I leave the criteria blank to sum all
gt; cells... You cannot leave a SUMIF criteria blank, and if you meant you used
gt; quot;quot;, that will total all cells with nothing ion, which would sum to 0
gt; anyway.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Becksterquot; gt; wrote in message
gt; ...
gt; gt; I have a range of cells which retrurn a value if I use the Sum function.
gt; gt; They are all formatted as numbers. However if I use Sumif it always
gt; returns
gt; gt; zero, even if I leave the criteria blank to sum all cells.
gt;
gt;
gt;

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=quot;quot;),--(M11:M28))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Becksterquot; gt; wrote in message
...
gt; Sorry Bob I mean I used quot;quot; which should sum corresponding blank cells. To
gt; use the actual numbers
gt;
gt; =SUMIF(G11:G28,quot;quot;,K11:K28)
gt;
gt; where K11:k28 is a sum_range range of numbers works fine, but if if use a
gt; different sum_range with the same range and criteria I get a zero result!
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; What do you mean by ...even if I leave the criteria blank to sum all
gt; gt; cells... You cannot leave a SUMIF criteria blank, and if you meant you
used
gt; gt; quot;quot;, that will total all cells with nothing ion, which would sum to 0
gt; gt; anyway.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Becksterquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I have a range of cells which retrurn a value if I use the Sum
function.
gt; gt; gt; They are all formatted as numbers. However if I use Sumif it always
gt; gt; returns
gt; gt; gt; zero, even if I leave the criteria blank to sum all cells.
gt; gt;
gt; gt;
gt; gt;
Hey Bob the were in the correct format but I've just managed to get it to
work! Thanks for your help.

quot;Bob Phillipsquot; wrote:

gt; Are you sure that the different sum range really does contain numbers and is
gt; not formatted as text.Test it with this formula
gt;
gt; =SUMPRODUCT(--(G11:G28=quot;quot;),--(M11:M28))
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Becksterquot; gt; wrote in message
gt; ...
gt; gt; Sorry Bob I mean I used quot;quot; which should sum corresponding blank cells. To
gt; gt; use the actual numbers
gt; gt;
gt; gt; =SUMIF(G11:G28,quot;quot;,K11:K28)
gt; gt;
gt; gt; where K11:k28 is a sum_range range of numbers works fine, but if if use a
gt; gt; different sum_range with the same range and criteria I get a zero result!
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; What do you mean by ...even if I leave the criteria blank to sum all
gt; gt; gt; cells... You cannot leave a SUMIF criteria blank, and if you meant you
gt; used
gt; gt; gt; quot;quot;, that will total all cells with nothing ion, which would sum to 0
gt; gt; gt; anyway.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Becksterquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; I have a range of cells which retrurn a value if I use the Sum
gt; function.
gt; gt; gt; gt; They are all formatted as numbers. However if I use Sumif it always
gt; gt; gt; returns
gt; gt; gt; gt; zero, even if I leave the criteria blank to sum all cells.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

Beckster

Sounds like the numbers are actually quot;textquot;.

Re-formatting to numbers will not do the trick.

Select an empty cell formatted as General and copy.

Select your range of quot;numbersquot; and Paste Specialgt;Addgt;OKgt;Esc.

This will force the numbers to be numbers.Gord Dibben MS Excel MVP

On Fri, 13 Jan 2006 01:40:02 -0800, quot;Becksterquot;
gt; wrote:

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