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

#### bnbcat

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

Region: UK

Summary

### 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
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:

Replies
3
Views
306
Replies
1
Views
202
Replies
1
Views
154
Replies
1
Views
232
Replies
3
Views
659

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?

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