SUMIFS with Multiple Criteria

Hi,

I work for a Fashion company. We have different departments in bags (day, evening and leather) and stores are given a grade in each department which relates to how many options of bags the store gets. The store then has planned sales by department.

I have simplified the sheet I am working on (have attached). In this example I have a list of stores and their grade in Day Bags, Eve Bags and Leather. I also have their planned sales for Day bags, Eve bags and Leather.

In the Planned Sales tab I want to sum the planned sales by grade, which is a sumifs. However, I want it to be able to find Day bags, Eve bags and Leather bags itself. So I have the below formula for the day bags grade 1 planned sales for example:

=SUMIFS(INDEX(GRADINGS!B:H,,MATCH('PLANNED SALES'!E4,GRADINGS!B3:H3,0)),GRADINGS!C:C,'PLANNED SALES'!D5)

This formula pulled back the correct planned sales for day bags. However, I also need it to find Grade columns by itself, which is where I am stuck. So for Day bags in the above formula I told it to look at column C:C in the 'Gradings' sheet (and then only sum if it says Grade 1). However, I need it to find the day bag column itself and then only sum if 1.

Please can anyone help with this? I have given the formula I got stuck on but it might well be that I am using the wrong formula and it is something completely different!

Thank you in advance for your help.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I will do it for you this time.)

You can use this instead:
=SUMPRODUCT((GRADINGS!$C$4:$C$9='PLANNED SALES'!$D5)*(GRADINGS!$D$3:$H$3='PLANNED SALES'!E$4)*GRADINGS!$D$4:$H$9)

Ah yes! I got that one wrong. Undercomplicating things... just for a change.