Precision Limitations Using SUMIF vs SUM

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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
ABCD
1AccountBalanceFunctionValue w/ 15 decimal place display
2Acct A139.86
3Acct A748.65
4Acct A259.56
5Acct A(1,148.07)SUMIF Acct A0.000000000000000
6Acct A(242.97)SUBTOTAL Acct A0.000000000000000
7Acct A242.97SUM Acct A0.000000000000000
8
9Acct B139.86
10Acct B748.65
11Acct B259.56SUMIF Acct B(0.000000000000028)
12Acct B(1,391.04)SUBTOTAL Acct B0.000000000000000
13Acct B242.97SUM Acct B0.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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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
ABCD
1AccountBalanceFunctionValue w/ 15 decimal place display
2Acct A139.86
3Acct A748.65
4Acct A259.56
5Acct A(1,148.07)SUMIF Acct A0.000000000000000
6Acct A(242.97)SUBTOTAL Acct A0.000000000000000
7Acct A242.97SUM Acct A0.000000000000000
8
9Acct B139.86
10Acct B748.65
11Acct B259.56SUMIF Acct B(0.000000000000028)
12Acct B(1,391.04)SUBTOTAL Acct B0.000000000000000
13Acct B242.97SUM Acct B0.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.
dunno.gif
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,402
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,818
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

=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
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.

Rich (BB code):
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. :biggrin:

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top