INDEX/MATCH question

[phillygirl777]

Hello,

Please see the attached example.

The yellow part of the data section shows percentage allocation of each product by region. The green section shows the allocation of the same product by distribution channel. Now I need to "marry" the two and get the sum by region by channel (at this point products don't matter).

My SUMIFS formula with INDEX and MATCH caused double-counting. Could you please help?

Thanks!!!!!

 

[Luke M]

Assuming that you would multiply each percentage against the other (so apples-north gets 100% then formula in B17
=SUMPRODUCT(G$2:G$11,INDEX($B$2:$E$11,,MATCH($A17,$B$1:$E$1,0)))

Copy across and down to D20

 

[phillygirl777]

It worked - thank you very much!!! Forgot all about good ol' SUMPRODUCT

 

[phillygirl777]

How about another iteration - what if we calculate it for Meat only (see Part 2), the red tab

 

[phillygirl777]

Assuming that you would multiply each percentage against the other (so apples-north gets 100% then formula in B17
=SUMPRODUCT(G$2:G$11,INDEX($B$2:$E$11,,MATCH($A17,$B$1:$E$1,0)))

Copy across and down to D20

Thanks again for posting your reply!!! Any idea how to add another element? Thank you!!!!

 

[GraH - Guido]

Duplicated here https://chandoo.org/forum/threads/p...sumproduct-for-am-additional-criterion.43460/

 

[Luke M]

Closed this thread due to duplication.