# 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 ?