SUMPRODUCT for multiple sheets and columns

Hi everyone!,

I was hoping that someone could help me out with solving this issue.
In short, I have the following formula I wish to apply to multiple sheets: =SUMPRODUCT(((A3:A11=E15)*(B1:G1<=E16))*B3:G11)
Please see, the attached workbook I use to illustrate the problem Template SUMPRODUCT across multiple sheets.xlsx.

How could I re-formulate the SUMPRODUCT formula on Sheet 1 to make it so that it applies to the number across multiple sheets? For instance, Sheet 2 to 5.
I keep the criteria on the first sheet only. I have read quite some posts related to this issue and learned a bit about the INDIRECT funtion to apply a SUMIF formula across multiple sheets but I couldn't find this specific issue.

Please help!

-Xavier

Hi You gotta have individual formula in for each sheet.
Like in Sheet cell b21 you can have =SUMPRODUCT(((Sheet2!$A$3:$A$11=$E$15)*(Sheet2!$B$1:$G$1<=$E$16))*Sheet2!$B$3:$G$11)
and B22 you can have =SUMPRODUCT(((Sheet3!$A$3:$A$11=$E$15)*(Sheet3!$B$1:$G$1<=$E$16))*Sheet3!$B$3:$G$11) and so on..

Hi Anzafsa,

Yeah, this works fine!

I have used your formula in B21 now. This is the result. =SUMPRODUCT(((Sheet2!$A$3:$A$11=$E$22)*(Sheet2!$B$1:$G$1<=$E$23))*Sheet2!$B$3:$G$11)+SUMPRODUCT(((Sheet3!$A$3:$A$11=$E$22)*(Sheet3!$B$1:$G$1<=$E$23))*Sheet3!$B$3:$G$11)+SUMPRODUCT(((Sheet4!$A$3:$A$11=$E$22)*(Sheet4!$B$1:$G$1<=$E$23))*Sheet4!$B$3:$G$11)+SUMPRODUCT(((Sheet5!$A$3:$A$11=$E$22)*(Sheet5!$B$1:$G$1<=$E$23))*Sheet5!$B$3:$G$11)

Thanks a lot!

-Xavier

Happy to help..you are welcome