Feb 21 2022 12:02 PM
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.
Feb 21 2022 12:18 PM
=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.
Feb 21 2022 03:38 PM
@OliverScheurich thanks for your help. it didnt work. keeps returning zero.
Feb 21 2022 04:01 PM
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?
Feb 21 2022 05:32 PM
Thank you for sharing your file. Please see attached sheet. This could be what you are looking for.
Feb 21 2022 05:43 PM
Feb 22 2022 10:47 AM
Solution=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 ".
Feb 22 2022 10:47 AM
Solution=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 ".