# MMULT or SUMPRODUCT across Multiple Sheets with Indirect

#### jonathangranger

##### New Member
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

(If needed, actual file is here MMULT Question.xlsx)

Thank you!

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### jonathangranger

##### New Member
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!

Replies
3
Views
212
Replies
3
Views
131
Replies
1
Views
172
Replies
1
Views
80
Replies
4
Views
119