Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Sumif function with multiple criteria | Excel Worksheet Functions | |||
SumIF function | Excel Discussion (Misc queries) | |||
Using SUM function with #N/A in some cells | Excel Worksheet Functions | |||
IF function to blank without getting #value in sum function | Excel Worksheet Functions |