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 ?