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'd be curious to see such an example.

I know, I wish I'd saved at least one. But there really were times when I had to change the syntax.

 

[jtakw]

I know, I wish I'd saved at least one. But there really were times when I had to change the syntax.

Hi again,

I think I realize what the difference is with using the double minus "--" (I think), if -- is used, you can enter the ranges as it's own separate array, if -- is not used, then use asterisk "*" between arrays which makes them treated as ONE array so -- is not needed. Can someone explain this better please...

SAMPLE:

Excel 2010
	A	B	C	D
1	State	Sales
2	UT	75		275
3	CO	100		275
4	TX	125		0
5	CO	125
6	TX	150
Sheet1

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--(A2:A6="TX"),B2:B6)
D3	=SUMPRODUCT((A2:A6="TX")*B2:B6)
D4	=SUMPRODUCT((A2:A6="TX"),B2:B6)

 

[shg]

In the first syntax, SUMPRODUCT is passed two arrays, one comprised of 1s and zeros, and the other whatever is in col B -- golden.

In the second, SP is passed a single array; the expression evaluator has already converted the T/F array to 1s and 0s (because of the pending multiplication), then performed the multiplication - also golden.

In the third, SP gets two arrays, one of which is Boolean, and can't manage.

 

[sheetspread]

the expression evaluator has already converted the T/F array

And you can't think of any case offhand where that might cause problems, say formulas with more components?

 

[shg]

I've never seen one, so no.

 

[joeu2004]

As a follow up, is there a way to add a wild card to the formula?
[....]
=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
[....]

{ =SUM(IF(LEFT('NAV File'!$B$2:$B$1000,2)=A78,ROUND('NAV File'!$AA$2:$AA$1000,2))) }

Formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

 

[joeu2004]

you might need to put a -- or 0+ in front of the navfile=a8 part.

Agree with sheetspread [....] try it like this:
=SUMPRODUCT(--('NAV File'!B:B=A8)*ROUND('NAV File'!AA:AA,2))

For your edification, that would be redundant.

I've seen many cases where the * didn't work but the -- or +0 did.

There are instances where SUMPRODUCT(--(condition),array) works, but SUMPRODUCT((condition)*array) does not. For example, when "array" includes cells with text.

And there are instances where SUMPRODUCT((condition)*array) works, but SUMPRODUCT(--(condition),array) does not. For example, when one is a column and the other is a row, or one is one-dimensional and the other is two-dimensional.

(But we must be very careful with the construction of such SUMPRODUCTs. The simple example above might not produce the desired outcome.)

But note that the difference is multiplication of two "parameters" v. actually passing two separate parameters separated by a comma (or semicolon in some regions).

There are no instances where SUMPRODUCT((condition)*array) fails, but SUMPRODUCT(--(condition)*array) works, or vice versa. That is, where double negate combined with multiplication behaves differently than multiplication alone.

That was the suggestion by jtakw that I said is redundant.

I (and apparently jtakw) interpreted sheetspread's suggestion to be the same, since there was no mention of also separating the parameters.

 

[sheetspread]

There are instances where SUMPRODUCT(--(condition),array) works, but SUMPRODUCT((condition)*array) does not. For example, when "array" includes cells with text.

Yes, that must be it. I just tried to write a demo though and it still was fine with the * s, ok may be a while until I run across a counterexample but I'll put it in this thread.

(I think), if -- is used, you can enter the ranges as it's own separate array, if -- is not used, then use asterisk "*" between arrays which makes them treated as ONE array so -- is not needed.

Yes, maybe I left out the extra parentheses in other attempts with * but can't recall.

 

[sheetspread]

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.

I'd be curious to see such an example.

Ok, not exactly the same, but:

Nice: =SUMPRODUCT(($G$3:$M$3="v")*($D$6:$D$36="Actual")*ISNUMBER($G$6:$M$36)*$G$6:$M$36) doesn't work with text but changing the last asterisk to a , does