# Precision Limitations Using SUMIF vs SUM

#### Jerry Sullivan

##### MrExcel MVP
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

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
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. #### AlphaFrog

##### MrExcel MVP
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
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

=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
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. 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.

Replies
0
Views
306

### Forum statistics

1,130,048
Messages
5,639,769
Members
417,111
Latest member ### 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.

### Which adblocker are you using?    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

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