Sumproduct/Indirect formula needed

[colinheslop1984]

Hello

Can someone please help re-write the formula. Not sure what I'm doing wrong, I get #REF error.

=SUMPRODUCT(INDIRECT("'"&C$1&"'!$c$5:$au$54")*(INDIRECT("'"&C$1&"'!$a$5:$a$47"=E2)*(INDIRECT("'"&C$1&"'!$c$1:$au$1"=E2)*(INDIRECT("'"&C$1&"'!$c$3:$au$3"="total"))))

C$1 = Worksheet reference
$c$5:$au$54 = Data Range
$a$5:$a$47 = Criteria range 1 on vertical axis
G2 = Criteria 1
$c$1:$au$1 = Criteria range 2 on horizontal axis
E2 = Criteria 2
$c$3:$au$3 = Criteria range 3 on horizontal axis
"total" = Criteria 3 on horizontal axis

Let me know if you need more info

I am using Excel 2016

 

[Fluff]

I am using Excel 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

You have the brackets in the wrong place & also the A5:A47 range is the wrong size.

=SUMPRODUCT(INDIRECT("'"&C$1&"'!$c$5:$au$54")*(INDIRECT("'"&C$1&"'!$a$5:$a$54")=E2)*(INDIRECT("'"&C$1&"'!$c$1:$au$1")=E2)*(INDIRECT("'"&C$1&"'!$c$3:$au$3")="total"))

 

[colinheslop1984]

You have the brackets in the wrong place & also the A5:A47 range is the wrong size.

=SUMPRODUCT(INDIRECT("'"&C$1&"'!$c$5:$au$54")*(INDIRECT("'"&C$1&"'!$a$5:$a$54")=E2)*(INDIRECT("'"&C$1&"'!$c$1:$au$1")=E2)*(INDIRECT("'"&C$1&"'!$c$3:$au$3")="total"))

I still get a #REF error, not sure why

 

[colinheslop1984]

I still get a #REF error, not sure why

Never mind, I made an error. Its fixed and works great. Thank you

 

[Fluff]

You're welcome & thanks for the feedback.