sumifs formula

[Kathy_j2290]

I run a commercial kitchen. I have set up a spreadsheet using sumifs to bring in required information. I am stuck on one formula. I want the sumif to read and match a cell in the worksheet then use several criteria in the data. for example, match cell a3 (which has the name of the meal e.g lemon chicken) sort through data in another tab and sum the quantity for "hot" lemon chicken. The data is in columns with meal names, temperature, quantities. For some reason I cant work out where to put the reference cell for it to match the information to. I don't want to write "lemon Chicken" into the formula because I need to make the spreadsheet dummy proof for staff that cant use excel so I need them to choose a meal name in the cell it is referencing.

[#2]

@Kathy_j2290 

=SUMIFS(F4:F15,D4:D15,A3,E4:E15,A4)

Maybe with this formula. In A3 and A4 are dropdowns in order to select the meal name and the temperature. The formula in cell B18 returns the quantity of the selection. 

[#3]

@OliverScheurich thanks for your help. it didnt work. keeps returning zero.

[#4]

@Kathy_j2290 

You are welcome. Most probably your data has different layout. I also was only guessing what could be meant by "hot" lemon chicken. Can you attach a file without sensitive data which shows the actual layout of your data?

[#5]

I will send a screen shot and hide the client names.

[#6]

@OliverScheurich 

[#7]

@Kathy_j2290 

Thank you for sharing your file. Please see attached sheet. This could be what you are looking for.

[#8]

Yes that is how i want it to work. what formula did you use to achieve it?

[#9 · Accepted solution]

@Kathy_j2290 

=SUMPRODUCT((MMULT((Tabelle2[Category]="Main Meal")*((Tabelle2[Meal]=A3)+(Tabelle2[Meal]=Tabelle1!A5)+(Tabelle2[Meal]=Tabelle1!A7)),ROW($1:$1)^0))*Tabelle2[Qty])

In order to calculate the "Properties" in cell B15 i used the above formula for example. Enter the formula as arrayformula with ctrl+shift+enter if you don't work with Office365 or 2021.

If you want to add meals to the table in sheet "Tabelle1" in range G6:G18 you have to adapt the formulas in cells B9 and B12. Currently there are 12 meals and the formulas in cells B9 and B12 contain the expression  " ROW($1:$12)^0 ". If you had 20 meals in this table you would have to change this expression/part of the formula to " ROW($1:$20)^0 ".

[#10]

thanks for your help. appreciate it