In column D I own a bunch of details balance.
In column E I hold a bunch of coupons.
If I want to find the average coupon I can use the formula
=SUMPRODUCT(D2:D461,E2:E461)/SUM(D2:D4...
Account criteria is in column I. Does anyone know a formula that I could use to find the average coupon for respectively set of criteria. For example, I'm using:
=SUMIF('Data'!$I:$I,'New Volume'!E$8&" "&'New Volume'!$A$7,'Data'!$D:$D)
To obtain the hot volume at respectively revise contained by type, but I can't feel of a mode to do this near the sumproduct function. Any abet would be greatly appreciated.
Answers: I'm assuming the 'Data'!$D:$D is equal as the column D surrounded by your first formula for the average.
This formula will multiply the vindication balance by the coupon for in recent times a single efficacy contained by column I, and will afterwards divide that by the sum of the vindication balance for that I importance:
=SUMPRODUCT(('Data'!$I:$I = 'New Volume'!E$8&" "&'New Volume'!$A$7) * ('Data'!D:D) * ('Data'!$E:E)) / SUMPRODUCT(('Data'!$I:$I = 'New Volume'!E$8&" "&'New Volume'!$A$7) * ('Data'!D:D))
As surrounded by your example, this checks whether respectively criterion within column I is equal to 'New Volume'!E$8&" "&'New Volume'!$A$7.
If it is, it multiplies D * E.
Then, for the numerator, it does duplicate entry again, until that time tallying the effectiveness contained by D.
If at hand is any other notes within these columns, you may own to replace the column reference (e.g. D:D), near full cell reference (D2:D461) to acquire the formulas to work.
If you obligation more info, make the addition of more details or e-mail.
Let us know if this is on the right track.
Cheers.
Put a creative roll of coupons contained by a column. I am assuming column H. The formula would be:
= SUMPRODUCT( (E2:E461=H2) * (D2:D461) )
The first criteria array resolves to True or false (1 or 0) and the matched pairs are multiplied.
You can hold as masses criteria arrays as you resembling.