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

1. ## 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!!

2. ## Re: Multiple Criteria Calculation both Criteria sit in the one column in multiple location

maybe like this.

3. ## Re: Multiple Criteria Calculation both Criteria sit in the one column in multiple location

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.

4. ## Re: Multiple Criteria Calculation both Criteria sit in the one column in multiple location

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

5. ## Re: Multiple Criteria Calculation both Criteria sit in the one column in multiple location

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

6. ## Re: Multiple Criteria Calculation both Criteria sit in the one column in multiple location

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?

7. ## Re: Multiple Criteria Calculation both Criteria sit in the one column in multiple location

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.

8. ## Re: Multiple Criteria Calculation both Criteria sit in the one column in multiple location

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

9. ## Re: Multiple Criteria Calculation both Criteria sit in the one column in multiple location

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

10. ## Re: Multiple Criteria Calculation both Criteria sit in the one column in multiple location

Originally Posted by oeldere
maybe like this.
Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

There are currently 1 users browsing this thread. (0 members and 1 guests)