Precision Limitations Using SUMIF vs SUM

[Jerry Sullivan]

I wasn't aware that precision could be an issue using SUMIF with
dollars and cents values (2 decimals), but apparently it is.

In the example below, both nearly identical Accounts have a SUM of exactly zero, however
the list of Account B yields a SUMIF result slightly less than zero.

Excel Workbook
	A	B	C	D
1	Account	Balance	Function	Value w/ 15 decimal place display
2	Acct A	139.86
3	Acct A	748.65
4	Acct A	259.56
5	Acct A	(1,148.07)	SUMIF Acct A	0.000000000000000
6	Acct A	(242.97)	SUBTOTAL Acct A	0.000000000000000
7	Acct A	242.97	SUM Acct A	0.000000000000000
8
9	Acct B	139.86
10	Acct B	748.65
11	Acct B	259.56	SUMIF Acct B	(0.000000000000028)
12	Acct B	(1,391.04)	SUBTOTAL Acct B	0.000000000000000
13	Acct B	242.97	SUM Acct B	0.000000000000000
Sheet

These articles shed some light on the issue....

http://support.microsoft.com/kb/78113
http://www.cpearson.com/excel/rounding.htm

...However I hoping someone can help explain why SUMIF has this limitation but SUM and SUBTOTAL don't?

 

[T. Valko]

I wasn't aware that precision could be an issue using SUMIF with
dollars and cents values (2 decimals), but apparently it is.

In the example below, both nearly identical Accounts have a SUM of exactly zero, however
the list of Account B yields a SUMIF result slightly less than zero.

Excel Workbook
	A	B	C	D
1	Account	Balance	Function	Value w/ 15 decimal place display
2	Acct A	139.86
3	Acct A	748.65
4	Acct A	259.56
5	Acct A	(1,148.07)	SUMIF Acct A	0.000000000000000
6	Acct A	(242.97)	SUBTOTAL Acct A	0.000000000000000
7	Acct A	242.97	SUM Acct A	0.000000000000000
8
9	Acct B	139.86
10	Acct B	748.65
11	Acct B	259.56	SUMIF Acct B	(0.000000000000028)
12	Acct B	(1,391.04)	SUBTOTAL Acct B	0.000000000000000
13	Acct B	242.97	SUM Acct B	0.000000000000000
Sheet

These articles shed some light on the issue....

http://support.microsoft.com/kb/78113
http://www.cpearson.com/excel/rounding.htm

...However I hoping someone can help explain why SUMIF has this limitation but SUM and SUBTOTAL don't?

Hmmm...

Very interesting!

Never saw a floating point error that was dependent upon the type of sum function being used.

I don't have an explanation.

 

[AlphaFrog]

I don't have an answer, but it is interesting.

For what it's worth, SUMPRODUCT seems to return the same value as SUMIF

=SUMPRODUCT(--($A$2:$A$13=$A9),$B$2:$B$13)

 

[Jonmo1]

I don't know the actual programming behind exactly how sumif works..
But if Sumif works anything like Sumproduct..

Then the difference is that sumif/sumproduct actually does multiplication as well as addition, while sum doesn't.
That may be where the precision jumps into play.

 

[ZVI]

=SUMIF(A2:A13,A9,B2:B13) is equal to =SUM(A2:A13*1) or =SUMPRODUCT(--(A2:A13=A9),B2:B13) which not return zero value
because of Floating-point arithmetic may give inaccurate results in Excel
There are a lot of such inaccurate results just for the single math operation, for example:
=42655.66-33256.06 returns 9399.60000000001

 

[ZVI]

My misprint:

=SUM(A2:A13*1)

Should be: =SUM(B9:B13*1)

 

[Jerry Sullivan]

Thank you all for your replies.

It's interesting to see that such a simple example as the one Vladimir provided would demonstrate the fact
that Floating-point arithmetic may give inaccurate results.

There are a lot of such inaccurate results just for the single math operation, for example:
=42655.66-33256.06 returns 9399.60000000001

While that seems pretty well understood, it still leaves an interesting puzzle as to why for certain combinations of
numbers SUM returns a different result than SUMIF or SUMPRODUCT.

jonmo1's idea might be right that sumif/sumproduct actually does multiplication as well as addition, while sum doesn't.

Doing some testing with VBA I found these interesting results, which seem to take multiplication out of the mix.

Sub Tries()

Debug.Print "Try1: " & 100 + 100.02 - 200.02 
'returns: Try1: -1.4210854715202E-14

Debug.Print "Try2: " & 100 + 100.04 - 200.04 
'Try2: 1.4210854715202E-14

Range("A1") = 100
Range("B1") = 100.02
Range("C1") = -200.02
Debug.Print "Try3: " & Application.Sum(Range("A1:C1")) 
'Try3: 0

Debug.Print "Try4: " & Application.Sum(Range("A1"), Range("B1"), Range("C1")) 
'Try4: 0

Dim dblA1#, dblB1#, dblC1#
dblA1 = Range("A1")
dblB1 = Range("B1")
dblC1 = Range("C1")
Debug.Print "Try5: " & Application.Sum(dblA1, dblB1, dblC1) 
'Try5: -2.8421709430404E-14

End Sub

For the record, I realize this is an esoteric issue and I'm not doing anything that has be accurate to 15 decimal places.

I tripped accross this when an =IF(X=Y,.... formula gave an expected FALSE result for two values that should have been equal.

Going forward, I'll simply use a test like =IF(X-Y<0.0001,....; however i'm still a bit stunned to learn this is needed for
addition of 2 values that are already round at 2 decimal places.

Thanks again for your help.