Sumif with mapping multiple the same rows

Question

I want to write excel formula that will sumif the values based on a match of the rows. The problem is that I the cells to be matched can occur multiple times and basing on the Mapping legend they should return the sum of values.

In the yellow cells I am trying to caluclate sumif of values in range B9:B14. In formula I am using index to match the names and to return the sum of all that is assined to Item1, then Item2 and Item 3. Unfortunately, Index gives only the match with the first met not with the all that I have in mapping legend. Does Anybody has idea how to write this formula?

Here is the formula that I use (wrong) and in cell C4 and C5 is the result that should be achieved for Item 2 and Item 3:

:

Item 1 is ok because it does not have more than 1 mapping.

Answer 1

You can also use the following in B3 and drag down.

Enter as an array formula using Ctrl+Shift+Enter

=SUMPRODUCT(--($A$10:$A$15=IF($F$3:$F$8=$A3,$G$3:$G$8)),$B$10:$B$15)

This works because the arrays are of equal length and items appear in the same order.

Answer 2

its very simple no need to complicate with index functions.

First you need to cross ref the mapping legend with your inputs using sum-if function and then do that again to get individual values from each items.

Sumif in action

Sumif visualized

With refrence to the image put this formula in H3 to H8

=SUMIF($A$10:$A$15,G3,$B$10)

and then this formula in B3 to B5

=SUMIF($F$3:$F$8,A3,$H$3:$H$8)

Answer 3

You can easily accomplish this in two steps:

1. In H3 enter formula: =SUMIF($A$10:$B$15, G3, $B$10:$B$15). Drag it all the way down until 8th row.

2. In B3 enter formula: =SUMIF($F$3:$H$8, A3, $H$3:$H$8). Drag it all the way down until 5th row.

Your results should be like this: