Index Match with Summing?

[Battletoads]

I have a table that references and compiles cells from other tables. The table is divided into 2 countries (Canada & US) and further divided into 4 provinces/states with a total column for each region. I am wondering if there is a way for another user (who knows little about Excel) to easily lookup how many of each product a specific region has sold. An index match works perfectly for what I need, but the only wrench that gets thrown in is, there is a specific class of products (1) that have multiple sub-classes (1A-1G) and I am not so much interested in how much each individual class sells as much as how many class 1 products in total I sell, but I still need to keep them separated. An easy work around is just highlighting the total column for classes 1A-1G and seeing the total in the corner but I will eventually be expanding to many more regions, classes & sub-classes and would like for other users to have an easy drop-down lookup function.

[José Augusto]

Try this

Please Login or Register  to view this content.

[FlameRetired]

Similar to José's, but with a slightly different interpretation.

Please Login or Register  to view this content.

You may need to adjust the wildcard to something else suitable for your actual data.

[CARACALLA]

=sumproduct((b2:k2=d17)*(isnumber(match(a3:a13,a3:a9,0)))*(b3:k13))