Sumif formula not working - Excel emergency!

[DogGoneGood]

I am trying to do a sumif in excel and am getting the incorrect results. As seen in the below table. I am doing a sumif to return the sum of values for particular codes that begin with the number zero. Excel is summing up the values for both codes 0E1 and 0E2 instead of treating these codes as unique. How can I resolve this? Your prompt response would be greatly appreciated.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Code Value Sumifformula Sumifresult
0E1 10 =SUMIF($A:$A,A1,$B:$B) 45
0E1 20 =SUMIF($A:$A,A2,$B:$B) 45
0E2 5 =SUMIF($A:$A,A3,$B:$B) 45
0E2 10 =SUMIF($A:$A,A4,$B:$B) 45

 

[MrKowz]

It looks like your calculations may be turned off. Go to Tools>Options>Calculation and turn on automatic calculations.

 

[DogGoneGood]

I thought that could be it but I do have automatic calculation on. It looks like it is recognizing it as some kind of exponential number but I don't know how to fix it. I even tried putting a ' in front of the zeroes.

 

[MrKowz]

Ahh - that could very well be the problem.

Give this a shot:

=SUMPRODUCT(--($A$1:$A$100=TEXT(A1,"@")),$B$1:$B$100)

 

[DogGoneGood]

I got zeroes as the result?

 

[MrKowz]

Hmm - do you have more data I can use to play around with formulas? Or are you able to e-mail me your spreadsheet so I can work directly with your data? (If you opt for the e-mail, shoot me a PM and i'll give you my email addy)

 

[barry houdini]

Try just

=SUMPRODUCT((A$1:A$100=A1)+0,B$1:B$100)

 

[DogGoneGood]

Thanks Barry! That worked!

Do you happen to know why Excel has this issue?

 

[barry houdini]

The issue is as you surmised - SUMIF is incorrectly recognising your text values as some sort of exponential number.....and because excel uses only 15 significant digits it treats them all as the same number, hence incorrect results.

This is a problem with SUMIF/COUNTIF type formulas. One advantage of those formulas is text and number are treated (almost) the same so if you use

=COUNTIF(A:A,2)

that will count numeric 2s and text 2s......but the downside is that it treats any text that looks like a number as a number, like in this case or with 16 digit credit card numbers stored as text (where only the first 15 digits are recognised).

SUMPRODUCT distinguishes between text and number - which is sometimes a problem but means that here it just sees the text value "0E1" which is what you want.....

 

[DogGoneGood]

Wow. I wonder if Microsoft fixed this issue in Excel 2010?

Thanks for your help too Mr. Kowz!