Sumproduct(sumifs(indirect (1 Viewer)

[IainG]

Can anyone tell me if the SUMIFS function can work in this way?

I used something similar with the SUMIF from and earlier post which worked great, but now need to use multiple criteria.
This code is producing the #Value!

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$E8&"'!$G$6:$HZ$20"),INDIRECT("'"&$E8&"'!$F$6:$F$20"),$F8&"*",INDIRECT("'"&$E8&"'!$G$5:$HZ$20"),H$4))

Any help will be very much appreciated.

 

[NBVC]

The 3 ranges need to be the same size.

$F$6:$F$20 is one column wide whereas $G$6:$HZ$20 is 228 columns wide.

 

[IainG]

My actual data range (Sum Range) is (G6:HZ20)
Critera1 is Text (F6:F20)
Criteria2 is a Date (G5:HZ5)

You wouldn't happen to know if there is a function that will allow Column & Row criteria?

 

[NBVC]

Try:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20"),$F8&"*",INDEX(INDIRECT("'"&$E8&"'!$G$6:$HZ$20"),0,MATCH(H$4,INDIRECT("'"&$E8&"'!$G$5:$HZ$5"),0))))

 

[IainG]

That's giving me a #N/A

 

[NBVC]

Are you trying to match H4 to INDIRECT("'"&$E8&"'!$G$5:$HZ$5")?

is H4 a date too, and is it an exact match to what is in INDIRECT("'"&$E8&"'!$G$5:$HZ$5")?

Maybe try posting a sample workbook if still an issue...

 

[NBVC]

Actually, I am not even sure why you need the SUMPRODUCT...

If I understood, I think you only need:

=SUMIF(INDIRECT("'"&$E8&"'!$F$6:$F$20"),$F8&"*",INDEX(INDIRECT("'"&$E8&"'!$G$6:$HZ$20"),0,MATCH(H$4,INDIRECT("'"&$E8&"'!$G$5:$HZ$5"),0)))

 

[IainG]

I have attached a sample of the data.

Thank you.

 

[NBVC]

The years in Row 4 of Level2 sheet don't match the years in Row 5 of the other sheet....

 

[IainG]

Oh my head!

Sorry about that, I think I need a break....

Thank you for taking the time to help.