SUMPRODUCT SUMIFS across multiple worksheets errors

[Apaka]

Hi. I've been struggling to get a formula right and not having any success. I've researched this for hours and tried several similar solutions but am having no luck. I will have many worksheets with the same layout, but will need to summarize the totals, by criteria/field. To summarize, I am trying to use a SUMPRODUCT SUMIFS INDIRECT formula but keep getting errors and don't know why. I've tried the following formula

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Table2[MDEP]&"'!"&"J:J"),INDIRECT("'"&Table2[MDEP]&"'!"&"K:K"),E1,INDIRECT("'"&Table2[MDEP]&"'!"&"M:M"),A7))

and tried different variations for ranges (actual ranges, range names, etc.

I appreciate any help.

 

[pecoflyer]

Post a sample sheet please

 

[Apaka]

Thank you for your interest. I tried to upload the file last night but it was too large. I've reduced the file size significantly to upload. For this sample file, there are 4 tabs.

1. The Summary worksheet (where I am having the issue) has an attempt at a formula that will eventually have one for each cell.
2. MXCL is one of what will be many worksheets that need to be summarized (by IT Tower and Cost Pool) on the Summary worksheet.
3. MXUS another (duplicate) sample to be summarized (by IT Tower and Cost Pool).
4. A data validation worksheet. I've converted my tables to ranges to reduce file size.

There will eventually be dozens, if not over 100 worksheets to summarize, which is why I am trying to figure out a solution. I think SUMPRODUCT SUMIFS INDIRECT is the right solution but if there is a better one I am open to the suggestion.

The one restriction is the file cannot be an XLSM (VBA). That is a work restriction, which is why I didn't seek a solution using VBA (not that I am a VBA expert or anything but didn't even explore a solution).

Again, thank you.

 

[deciog]

Apaka

Check if this is how you want it

Decio

 

[Peter Bartholomew]

I converted the book to Tables and structured references simply because I can follow what is going on better that way.
It came as something of a surprise to me that the formula would sum over sheets rather than giving #VALUE! errors, so I have learnt something about the use of INDIRECT!

I expect Decio's solution will suit you better, but I have yet to take a look.

 

[Apaka]

Deciog and Peter,

Thank you both so much. Both ways provide a solution, which I truly needed. Of interest, I see where Deciog's array worked while mine didn't. I'm not sure what I did wrong other than perhaps hit CTRL-SHIT-ENTER too quickly, but do appreciate the fix.

For Peter's solution, thank you for showing me two things. I like how you structured your nested formulas and may use that myself in the future (easier reading). I also appreciate you sharing XLFN, which I wasn't familiar with.

Again, thank you both. Happy Holidays.

 

[deciog]

Apaka

Thanks for the feedback
Its formula just lacked the function IFERROR confers
Decio

 

[johnmcgregor]

There will eventually be dozens, if not over 100 worksheets to summarize, which is why I am trying to figure out a solution. I think SUMPRODUCT SUMIFS INDIRECT is the right solution but if there is a better domyessay I am open to the suggestion.

Thanks for this suggestion. I had similar issue on my file and the functions you've described helped me to solve it.