# SUMIFS - rounding values prior to summing

#### beckys

##### Board Regular
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!

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

##### Well-known Member
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

##### Banned user
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
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

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_0004 AX_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
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

##### Banned user

you might need to put a -- or 0+ in front of the navfile=a8 part.
=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(...).

##### Well-known Member
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
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
I'd be curious to see such an example.

Replies
5
Views
170
Replies
1
Views
150
Replies
0
Views
54
Replies
1
Views
334
Replies
1
Views
105

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,559
Messages
5,832,474
Members
430,136
Latest member
Asir Jefferson

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