SUMPRODUCT with dynamic range AND multiple criteria on the same row

Hello,

Attached are the image and the spreadsheet.

2017-01-26 12_16_15-sample.xlsx - Excel.png

As you can see on the top half of the image/sheet, the data was layout as is.

On the bottom half on the left hand side, I've made it by SalesPerson by Country. This is used as a 'list' for Data Validation. For example, looking at SalesPerson across the column, we can see that 'Albert' has responsibility in country 'Canada', 'USA' and 'Europe'. and his products are 'B', 'H' and 'M'.

Now, assume that when user select a value from the 'data validation', i.e. 'Albert', I'd like a formula that knows to take B21 to D21 as the lookup value and then, given another criteria (which is the month Jan to Dec), it'll take the value of each product and multiply it by the 'comission'.

My problem here is that, how can I make together the SUMPRODUCT, INDEX and MATCH to work together for the dynamic lookup range (which in this example is B21 to D21)

Hi Richard,

Thanks for your help!

I should have mentioned that because I'm trying to create a sample, there's row 2 and 3 shown there.

But in actuality, real data doesn't have that row 2 and 3. Only row 1 (commission) and 4 (product).

That's why I'm using data validation so that user can know which SalesPerson and/or Country has what Product.

Does that mean that there won't be a formula solution that enables to SUM "Jumping columns"?