• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX/MATCH question

Status
Not open for further replies.
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!!!!!
 

Attachments

  • Example 01.22.20.xlsx
    10.3 KB · Views: 3
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
 
How about another iteration - what if we calculate it for Meat only (see Part 2), the red tab
 

Attachments

  • Example 01.22.20.xlsx
    12.7 KB · Views: 1
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!!!!
 

Attachments

  • Example 01.22.20.xlsx
    12.7 KB · Views: 1
Status
Not open for further replies.
Back
Top