SUMIFS - rounding values prior to summing

beckys

Board Regular
Joined
Apr 19, 2005
Messages
116
I have a spreadsheet that has a list of numbers in column AA that go out to multiple decimals (i.e. 199.277444, 42.19343)
I'd like to write a sumifs formula that will sum the numbers in column AA when certain criteria in column B is met, but I want the individual values in column AA to be rounded prior to being summed up.

I've tried the following formula - SUMPRODUCT(('NAV File'!B:B=A8)*ROUND('NAV File'!AA:AA,2)), but get a #VALUE! error.
(The value in A8 is AX_0004, and this exact value appears in column B of the NAV File tab, so this isn't causing the error.)

Any other suggestions? Any help is much appreciated!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
I don't think the ROUND part is what's returning an error, you might need to put a -- or 0+ in front of the navfile=a8 part.
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,975
Office Version
  1. 2010
Platform
  1. Windows
I've tried the following formula - SUMPRODUCT(('NAV File'!B:B=A8)*ROUND('NAV File'!AA:AA,2)), but get a #VALUE! error.

First, it is generally a bad idea to use whole-column ranges like B:B and AA:AA, at least in Excel 2007 and later.

In this example, Excel must perform 4+ million operations and create 3+ million in temporary storage (probably more than 3MB).

Moreover, that is probably the root cause of the #VALUE error, if any of AA:AA is text.

So it is better to use ranges that are reasonably limited to where numeric values are or might be, allowing for reasonable expansion. For example, B2:B100000 and AA2:AA100000.

But secondly, if you must allow for your range to contain text, you can use the following array-entered formula (press ctrl+shift+Enter instead of just Enter):

=SUM(IF('NAV File'!B2:B100000=A8,ROUND('NAV File'!AA2:AA100000,2)))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,428
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Agree with sheetspread, you need to add -- to your formula because you can't multiply "AX_0004" with your numerical values in AA, thus causing the error, try it like this:

=SUMPRODUCT(--('NAV File'!B:B=A8)*ROUND('NAV File'!AA:AA,2))
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Your syntax is fine, but I would surely not use full-column references.

Row\Col
A​
B​
C​
AA​
AB​
AC​
1​
AX_0004
1.234​
4.23​
AB1: =SUMPRODUCT((B:B=A8) * ROUND(AA:AA,2))
2​
1​
3​
1​
4​
1​
5​
1​
6​
1​
7​
1​
8​
AX_0004AX_0004
1​
9​
1​
10​
1​
11​
1​
12​
AX_0004
1​
13​
AX_0004
1​
14​

Do you have #VALUE! errors in either column?
 
Last edited:

beckys

Board Regular
Joined
Apr 19, 2005
Messages
116
I adjusted to =SUM(IF('NAV File'!B2:B100000=A8,ROUND('NAV File'!AA2:AA100000,2))) and it worked like a charm. Thanks to all for the input!
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,975
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

you might need to put a -- or 0+ in front of the navfile=a8 part.
Agree with sheetspread, you need to add -- to your formula [....] try it like this:
=SUMPRODUCT(--('NAV File'!B:B=A8)*ROUND('NAV File'!AA:AA,2))

For your edification, that would be redundant.

There is nothing sacrosanct about the use of double negate (--) in SUMPRODUCT expressions and elsewhere.

Any arithmetic operation converts TRUE and FALSE into 1 and 0, then applies the operation.

So the multiplication is sufficient. TRUE*ROUND(...) is effectively 1*ROUND(...). FALSE*ROUND(...) is effectively 0*ROUND(...).
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
For your edification, that would be redundant.

There is nothing sacrosanct about the use of double negate (--) in SUMPRODUCT expressions and elsewhere.

Any arithmetic operation converts TRUE and FALSE into 1 and 0, then applies the operation.

So the multiplication is sufficient. TRUE*ROUND(...) is effectively 1*ROUND(...). FALSE*ROUND(...) is effectively 0*ROUND(...).

I've seen many cases where the * didn't work but the -- or +0 did. I really should have posted some examples here asking the experts why, just never got around to it. If anyone reading this can do so please feel free, I'll try also in the future.
 

beckys

Board Regular
Joined
Apr 19, 2005
Messages
116
As a follow up, is there a way to add a wild card to the formula?
I'm trying the following (as a CSE formula) and the total is coming up as 0.00, but should be much larger.

=SUM(IF('NAV File'!$B$2:$B$1000=A78&"*",ROUND('NAV File'!$AA$2:$AA$1000,2)))

In this case, A78 = AX, and the values in NAV File column B are:
AX_0004
AX_0001
AX_0005
AX_0002
AX_0006
AX_0003
BX_0005
BX_0001
BX_0006
BX_0002
BX_0007
BX_0003
BX_0008
BX_0004
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
I'd be curious to see such an example.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,054
Messages
5,599,533
Members
414,315
Latest member
Yolanda5050

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