SUMIF, does not distiguish between similar numbers

[Dannyh1]

Hi,

I have a situation where my SUMIF does not distinguish between 2 codes. Instead of summing them seperately, in adds them together.

The numbers are similar 6 digit numbers, but the last digit is different:

501393 = £4780.97
501392 = £51,989.76

My SUMIF result for both references = £56,770.73

This also happens for below codes

521160
521165

Has anyone come across this before/ have a solution?

Many Thanks.

 

[VoG]

I can't reproduce that

Excel Workbook
	A	B	C	D
1	501393	4,780.97		4780.97
2	501392	51,989.76		51989.76
Sheet1

 

[Dannyh1]

Peter apologies,

I have mis-posted. the Sumif is actually working off a combination of 2 codes (cost centre & gl)

the codes that do not seem to seperate are:

2709565100501392
2709565100501393

Many Thanks.

 

[T. Valko]

Hi,

I have a situation where my SUMIF does not distinguish between 2 codes. Instead of summing them seperately, in adds them together.

The numbers are similar 6 digit numbers, but the last digit is different:

501393 = £4780.97
501392 = £51,989.76

My SUMIF result for both references = £56,770.73

This also happens for below codes

521160
521165

Has anyone come across this before/ have a solution?

Many Thanks.

Try using the equivalent SUMPRODUCT formula and see if that makes a difference.

=SUMPRODUCT(--(A1:A10=code),B1:B10)

 

[T. Valko]

Peter apologies,

I have mis-posted. the Sumif is actually working off a combination of 2 codes (cost centre & gl)

the codes that do not seem to seperate are:

2709565100501392
2709565100501393

Many Thanks.

Ok, that's the problem.

The SUMIF function is only evaluating the first 15 characters of the 16 character string. This is a quirk in Excel where a couple of functions (COUNTIF(S) and SUMIF(S)) evalaute numeric numbers and text numbers as being equal. Excel only works on numbers to 15 significant digits so those 16 character strings are TEXT even though they look like numbers.

Then, Excel is getting confused and only evaluating the first 15 characters as numeric values so that's how you end up where you are.

The solution is to use the SUMPRODUCT function. It does not evaluate text numbers and numeric numbers as being equal.

 

[Dannyh1]

Excellent - thanks very much.

 

[T. Valko]

Excellent - thanks very much.

You're welcome. Thanks for the feedback!