Sum product with multiple column criteria, summed across x no. of sheets

bnbcat

New Member
Joined
Apr 27, 2016
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I'm trying to use sumproduct and indirect to sum the values of values across multiple sheets based on multiple criteria (1 criteria for rows, 2 criteria for columns).

The current formula I'm using (that doesn't work) is
Excel Formula:
{=SUMPRODUCT((INDIRECT("'"&Region&"'!$C4:$Q6"))*(INDIRECT("'"&Region&"'!$C2:$Q2")=$A$2)*(INDIRECT("'"&Region&"'!$C3:$Q3")=C$3)*(INDIRECT("'"&Region&"'!$B4:$B6")=$B4))}

I have a list "Region" with 4 regions (US, UK, MX, CA), 2 samples below. The last screenshot is what the summary I'm trying to create is supposed to look like. I've been Googling for a few hours but haven't been able to find a solution that could help my situation. Any help pls?

Region: US
1619212704029.png


Region: UK
1619212734607.png


Summary
1619212686538.png
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,984
Office Version
  1. 365
  2. 2010
Please use XL2BB to provide data so that no one has to retype everything to try to solve your problem.
It's unclear (at least to me) what answer(s) you want for, say, 2020, in the Summary sheet. Is it ONLY for Kiwi as your example illustrates?
(there are no headings for Kiwi in those years.)

Is the answer you want in Summary in C4, for example, the sum of the values in the same cells but in the other four sheets?

So, maybe you want C4 in the Summary (filled across and down) to be:
Code:
=SUM(US:CA!C4)

???
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,561
Members
418,140
Latest member
ahepple86

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
Top