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.

 

[ghamauricio]

Hello gentlemen,

I'm new in this forum, and I found it looking for a solution to a problem I'm having with the SUMIFS function.

I'm using SUMIFS to sum the values using three criteria, but two of them will be empty most of the time. The problem is that, according to what T. Valko described, if the condition is an empty cell, it doesn't match.

However, if I fill in with 0 in the first criteria comparison, it matches.

What is the solution for this?

Here's my data.
In this first spreadsheet (let's call it FIRST), I have chambers in a building and what kind of flooring I have on them.

Area Name	Area (m²)	Floor Type	External?	Special?
External	319,41	1	1	
Alojamento	46,125	1		
Vestiário	9,9	1		1
Banheiro	3,6	1		1
Alojamento 2	13,95	1

Now in this second spreadsheet (let's call it SECOND) I have what are the materials, and the spreadsheet is supposed to count how much of each stuff is going to be used:

Floor Type	External?	Special?	Total Area
1	1		319,41
1	1	1	0
1		1	13,5
2			9
1			122,125

Now in "Total Area" I have this:

=SUMIFS(FIRST!B:B,FIRST!C:C,SECOND!A:A,FIRST!D:D,SECOND!B:B,FIRST!E:E,FIRST!C:C)

The problem I'm having is: it doesn't consider empty cells as equal content. If I fill in 0's in the first spreadsheet it matches the empty cell in the second spreadsheet and returns correct results, but not the other way around.

Also, I would like to have them empty all the time; the only column used would be "Floor Type", and the others would be used only in special cases.

Any suggestions?

Thank you very much, and have a happy new year!