MMULT or SUMPRODUCT across Multiple Sheets with Indirect

[jonathangranger]

Hi everyone!

Need your expert help in summing across multiple sheets based on multiple criteria of different range sizes.

For example, I have 4 worksheets of recipes and 1 summary tab.
Each recipe contains a date, then ingredients with weights.

I'm looking to create summary tab where I can sum up the total weight, per ingredient, per date.

So far, I've managed to do a 3D sum to get the total weight per ingredient, like below. This works great.
=SUMPRODUCT(SUMIFS(INDIRECT("'"&AllTabNames&"'!C8:C20"), INDIRECT("'"&AllTabNames&"'!B8:B20"),IngredientTotal!$A2))

- AllTabName is the named range of all the tabs
- AllTabName!B8:B20 is where the ingredients are listed on each recipe
- AllTabName!C8:C20 is where the corresponding weights are listed on each recipe
- IngredientTotal!A2 is the criteria name for the ingredient

I'm now trying to add in the date criteria, but am having trouble. I tried using MMULTI, so I can get the ingredient weights into 1 array, like {10 kg, 2 kg, 3 kg}, and the matching date criteria into a 2nd array, like {1, 0, 1}. But I haven't managed to get it to work. This is what I tried

=MMULT((SUMIFS(INDIRECT("'"&AllTabNames&"'!C8:C20"), INDIRECT("'"&AllTabNames&"'!B8:B20"),IngredientTotal!$A2)),
_--(INDIRECT("'"&AllTabNames&"'!B3")=IngredientTotal!$B9))

- Teal color code s same as above
- AllTabNames!B3 is the date field on each recipe worksheet
- IngredientTotal!$B9 is the criteria date

Please help! Looking for non-VBA solutions.
(If needed, actual file is here MMULT Question.xlsx)

Thank you!

 

[jonathangranger]

Forgot to say that even when I add in Transpose to the second array in the MULTI formula, I still get a #VALUE! error. Maybe MMULT is not the answer? I would like a single value answer instead of an array, if possible. Thanks!