Sumif problem with zero value and blank cells

[yorkeyites]

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.

 

[T. Valko]

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.

 

[yorkeyite]

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

Thanks again

A follow up note, if I place <>"" in C1 and empty column A it still
totals up to 100.

 

[T. Valko]

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

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.

Thanks again

A follow up note, if I place <>"" in C1 and empty column A it still
totals up to 100.

 

[Joe Wildman]

Eg.

="State: "&IF('Sheet1'!$C$6="","",'Sheet1'!$C$6)

Use ,""

 

[yorkeyite]

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.