Forum Discussion
sumifs formula
=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 ".
=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 ".
- thanks for your help. appreciate it