MMULT or SUMPRODUCT across Multiple Sheets with Indirect

jonathangranger

New Member
Joined
May 20, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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!
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top