SUMIF does not use cell type information

Posted by Gabor Kocsis on February 04, 2002 7:44 AM

Hi All !

I have found a strange behaviour of the SUMIF function: it tries to use the text cells as numbers.

Try this : set A1 and A2 cell format to Text.
Enter 1.1 to A1, 2 to B1,
1.10 to A2 and 3 to B2.

For any other cell enter the following formula : =SUMIF(A1:A2,"1.1",B1:B2).

The calculated value is 5 (!) but the correcy answer is 2.
The same result is displayed for =SUMIF(A1:A2,"1.10",B1:B2).

So Excel cannot distinguish the "1.1" and "1.10" in the SUMIF, however thay are texts !

This is a real Excel problem. For example I want to use 1.1, 1.2, 1.3, ..., 1.10, 1.11 in the first column (as hierarchical chapter numbers), and want to summarize some values depending on this cell.

I know that the problem can be solved using VBA programming, it is no problem for me. But I want to avoid macros.

Any solution ?

Posted by Mark W. on February 04, 2002 7:56 AM

Use the array formula, {=SUM(IF(A1:A2="1.1",B1:B2))}

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.

Posted by Yogi Anand on February 04, 2002 6:41 PM

Hi Gabor and Mark:
What a dilemma you have us in!
After playing with it, trying out the N function, and the T function, the SumIf and the Sum(If array options, to me it seems that it would be most unobtrusive by changing "1.1" to "1.1.", and "1.10" to "1.10." -- then every thing works fine, then the SumIF and the Sum(If array formula, and so on give the same consistent result

so now with 1.1. in A1, 1.10. in B1, 2 in A2, and 3 in B2

=SumIf(A1:A2,"1.1.",B1:B2) gives 2
=SumIf(A1:A2,"1.10.",B1:B2) gives 3

and

array formula =Sum(If(A1:A2="1.1.",B1:B2)) gives 2

Nice Going Gabor!

Yogi Anand
ANAND Enterprises (broken link)

Posted by Gabor Kocsis on February 05, 2002 7:18 AM

Thank you very much !

The array formula is working.

The correct formula is {=SUM(IF(A1:A2="1.1",B1:B2,0))}

Gabor

Posted by Mark W. on February 11, 2002 5:11 PM

The 3rd IF argument, 0, isn't necessary. Without
it IF() will return FALSE which is ignored by SUM(). Thank you very much ! The array formula is working. The correct formula is {=SUM(IF(A1:A2="1.1",B1:B2,0))}