Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif problem with zero value and blank cells
I often use sumif formula in spreadsheets, the criteria usually is a
cell reference so the user can decide what they want to add up. I started to get some results for sumifs when the criteria cell was blank. On investigation I found that there were some zero values in the spreadsheet and that the formula was counting them. I thought a blank cell was a null value and not a zero value. I can duplicate this on different excel installations on different computers though the versions are all 2002. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif problem with zero value and blank cells
Some functions ignore empty cells and some evaluate empty cells as numeric
0. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif problem with zero value and blank cells
On Jan 31, 5:51*pm, "T. Valko" wrote:
Some functions ignore empty cells and some evaluate empty cells as numeric 0. SUMIF does both at the same time! ..........A..........B..........C 1...................10............ 2........0.........20............ 3........1.........30............ 4......=""........40............ Assume the empty cell C1 is your criteria cell. Cell A4 contains a formula blank. =SUMIF(A1:A4,C1,B1:B4) The empty criteria cell is evaluated as 0 yet *empty* cells in the criteria range are not so the result of the formula is 20 summing only row 2. If cell C1 held the number 0 the result would be the same, 20. Now, consider this... With the criteria cell being empty, what should the correct result be? What if your criteria was empty or blank cells? So you enter a formula blank in C1 as the criteria: ="". Now the result of the formula is 50, summing rows 1 and 4!!!! There's no standard logic as to how some functions handle these situations. It's just something you learn through experience. -- Biff Microsoft Excel MVP wrote in message ... I often use sumif formula in spreadsheets, the criteria usually is a cell reference so the user can decide what they want to add up. I started to get some results for sumifs when the criteria cell was blank. On investigation I found that there were some zero values in the spreadsheet and that the formula was counting them. I thought a blank cell was a null value and not a zero value. I can duplicate this on different excel installations on different computers though the versions are all 2002. Thanks again A follow up note, if I place <"" in C1 and empty column A it still totals up to 100. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif problem with zero value and blank cells
Yeah, that one doesn't make any sense. Here are some more unusual "trick
criteria" : Try these and see what results you get C1: = (just a plain equal sign) C1: < C1: * -- Biff Microsoft Excel MVP "yorkeyite" wrote in message ... On Jan 31, 5:51 pm, "T. Valko" wrote: Some functions ignore empty cells and some evaluate empty cells as numeric 0. SUMIF does both at the same time! ..........A..........B..........C 1...................10............ 2........0.........20............ 3........1.........30............ 4......=""........40............ Assume the empty cell C1 is your criteria cell. Cell A4 contains a formula blank. =SUMIF(A1:A4,C1,B1:B4) The empty criteria cell is evaluated as 0 yet *empty* cells in the criteria range are not so the result of the formula is 20 summing only row 2. If cell C1 held the number 0 the result would be the same, 20. Now, consider this... With the criteria cell being empty, what should the correct result be? What if your criteria was empty or blank cells? So you enter a formula blank in C1 as the criteria: ="". Now the result of the formula is 50, summing rows 1 and 4!!!! There's no standard logic as to how some functions handle these situations. It's just something you learn through experience. -- Biff Microsoft Excel MVP wrote in message ... I often use sumif formula in spreadsheets, the criteria usually is a cell reference so the user can decide what they want to add up. I started to get some results for sumifs when the criteria cell was blank. On investigation I found that there were some zero values in the spreadsheet and that the formula was counting them. I thought a blank cell was a null value and not a zero value. I can duplicate this on different excel installations on different computers though the versions are all 2002. Thanks again A follow up note, if I place <"" in C1 and empty column A it still totals up to 100. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif problem with zero value and blank cells
Eg.
="State: "&IF('Sheet1'!$C$6="","",'Sheet1'!$C$6) Use ,"" |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif problem with zero value and blank cells
On Feb 1, 9:54*am, Joe Wildman
wrote: Eg. ="State: "&IF('Sheet1'!$C$6="","",'Sheet1'!$C$6) Use ,"" I suppose I should be philosophical this but irritation is winning. I will have to go back and review all my spreadsheets that are circulating in the wild. Thanks again it has been an education. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif returns blank if all cells blank | Excel Worksheet Functions | |||
SUMIF except for blank cells | Excel Discussion (Misc queries) | |||
SUMIF cells in Column B are blank | Excel Worksheet Functions | |||
Sumif Cells Are Not Blank | Excel Worksheet Functions | |||
Using SUMIF with non-blank cells | Excel Worksheet Functions |