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

[bnbcat]

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

{=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

 

[kweaver]

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:

=SUM(US:CA!C4)

???