SUMIFS - rounding values prior to summing

[beckys]

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!

 

[sheetspread]

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]

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]

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]

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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

AA​

[/td][td]

AB​

[/td][td]

AC​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]AX_0004[/td][td][/td][td]

1.234​

[/td][td]

4.23​

[/td][td]AB1: =SUMPRODUCT((B:B=A8) * ROUND(AA:AA,2))[/td][/tr]
[tr][td]

2​

[/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]AX_0004[/td][td]AX_0004[/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td][/td][td][/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td][/td][td]AX_0004[/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td][/td][td]AX_0004[/td][td][/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Do you have #VALUE! errors in either column?

 

[beckys]

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]

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]

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]

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]

I'd be curious to see such an example.