Multiple Criteria Calculation both Criteria sit in the one column in multiple locations.

Im trying to find an efficient way to calculate a weighted total in the attached spreadsheet.

My issue is that the criteria exist within the same column so i have tried a criteria based sumproduct with array but having no luck.

There is a lengthy way to do it i have written the layout of the result below.

(Business A (Margin * Sales) + Business B (Margin * Sales) + Business C (Margin * Sales)) / Sumif (Sales)

But the issue is my actual work sheet has about 20 businesses, so this will be a very inefficient method to do this.

Any help would be greatly appreciated!!

Try this in B18

=SUMPRODUCT(--($A$3:$A$18="% Margin"),($B$5:$B$20),($B$3:$B$18))/SUMPRODUCT(($A$3:$A$15="Retail Sales")*($B$3:$B$15))

Note that the B range (in red) is OFFSET to reference the "Retail Sales" values.

Amended as got the ranges wrong ....

=SUMPRODUCT(--($A$3:$A$13="% Margin"),($B$5:$B$15),($B$3:$B$13))/SUMPRODUCT(($A$3:$A$15="Retail Sales")*($B$3:$B$15))

Perfect, thanks a lot!! Exactly what i was looking for can i ask what the '--' stand for?

Originally Posted by JohnTopley

Amended as got the ranges wrong ....

=SUMPRODUCT(--($A$3:$A$13="% Margin"),($B$5:$B$15),($B$3:$B$13))/SUMPRODUCT(($A$3:$A$15="Retail Sales")*($B$3:$B$15))

Perfect, thanks a lot!! Exactly what i was looking for can i ask what the '--' stand for?

Originally Posted by oeldere

maybe like this.

Thanks for the response that works, however i was looking for a solution that could be resolved within a formula without adding additional fields.

The "--" simply converts TRUE/FALSE to 1/0

this will work just as well

=SUMPRODUCT(($A$3:$A$13="% Margin")*($B$5:$B$15)*($B$3:$B$13))/SUMPRODUCT(($A$3:$A$15="Retail Sales")*($B$3:$B$15))