Select column based on two separate cells and then SUMIFS

[ri0tp00f]

Hello,

I am trying to create a dynamic sales report whereby a user can enter the month and week of that month into the spreadsheet in order to automatically generate sales results based on multiple criteria.

I know how to use SUMIFS in order to sum what I need based on criteria in multiple columns, however, I don't know how to get Excel to select the appropriate sum range based on two other cells.

In the file attached, I would like a user to be able to enter in month number (i.e. 1 for January etc.) in cell C13, and week of that month in C14 in order to populate the green cells C15, C16, C18, and C19 with sums based on the criteria of the salesperson (1 or 2) and Store type (store or kiosk). I've filled in the green cells with what the numbers should be for Month 1, Week 1, but a user should be able to change the Month and Week numbers in the yellow cells in order to repopulate the sales results in the green cells based on the different time period.

If there is an easier way that I don't need to list the Month# and Week# in rows 2 and 3 I'd also be interested to hear.

Thank you!

multiple criteria sum.xlsx

[Swapnanjali]

=SUMPRODUCT(($A$4:$A$11="Salesperson 1")*($B$4:$B$11="Store")*(C4:C11))
=SUMPRODUCT(($A$4:$A$11="Salesperson 1")*($B$4:$B$11="Store")*(D4:D11))
=SUMPRODUCT(($A$4:$A$11="Salesperson 1")*($B$4:$B$11="Store")*(E4:E11))
Above formulas are for Sales Person 1 week 1,2,3(store)

Below formulas are for sales person 1 week 1,2,3(Kisosk)
=SUMPRODUCT(($A$4:$A$11="Salesperson 1")*($B$4:$B$11="Kiosk")*(C$4:C$11))
=SUMPRODUCT(($A$4:$A$11="Salesperson 1")*($B$4:$B$11="Kiosk")*(D$4:D$11))
=SUMPRODUCT(($A$4:$A$11="Salesperson 1")*($B$4:$B$11="Kiosk")*(E$4:E$11))
Below formulas are for Sales Person 2 week 1,2,3(store)
=SUMPRODUCT(($A$4:$A$11="Salesperson 2")*($B$4:$B$11="Store")*(C4:C11))
=SUMPRODUCT(($A$4:$A$11="Salesperson 2")*($B$4:$B$11="Store")*(D4:D11))
=SUMPRODUCT(($A$4:$A$11="Salesperson 2")*($B$4:$B$11="Store")*(E4:E11))
Below formulas are for Sales Person 2 week 1,2,3(store)
=SUMPRODUCT(($A$4:$A$11="Salesperson 2")*($B$4:$B$11="Kiosk")*(C$4:C$11))
=SUMPRODUCT(($A$4:$A$11="Salesperson 2")*($B$4:$B$11="Kisok")*(D$4:D$11))
=SUMPRODUCT(($A$4:$A$11="Salesperson 2")*($B$4:$B$11="Kisok")*(E$4:E$11))

Sorry i am not able to upload the excelsheet.
Please let me if it works or not..

=SUMPRODUCT(($A$4:$A$11="Salesperson 2")*($B$4:$B$11="Kiosk")*(C$4:C$11))

[ri0tp00f]

Thank you for your response, but I'm certain there is a much easier way and the method you've responded with isn't what I'm looking for.

This wasn't my actual data I'm using, just a simplified version. The real version has all 12 months with 52 weeks. You shouldn't need to make a separate formula for each separate person, week, and store type.

There should only be four formulas needed, one for each green cell.