I have the following formula:
=SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=A7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))
I will need to apply this entire formula to other sheets besides Sheet1 and sum the totals, similar to this:
=SUM(
SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=A7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))
SUM((Sheet2!$E3:$E65535)*(Sheet2!$H3:$H65535=$A$2)*(Sheet2!$I3:$I65535=A7)*(Sheet2!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet2!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))
SUM((Sheet3!$E3:$E65535)*(Sheet3!$H3:$H65535=$A$2)*(Sheet3!$I3:$I65535=A7)*(Sheet3!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet3!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))
)
I will need to expand this formula over time as more sheets are added. Also the names of the sheets will not be Sheet1, Sheet2, etc. and will not follow any sort of pattern. Also the formula will be used in each cell of a large table with variations in the DATE ranges.
Is there any way to take the base equation, some sort of FOR loop, and a list of the sheet names to make the implementation easier? That way when a new sheet is added all I have to do is add it to the list of sheets and all of the cells will update their formulas automatically?
=SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=A7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))
I will need to apply this entire formula to other sheets besides Sheet1 and sum the totals, similar to this:
=SUM(
SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=A7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))
SUM((Sheet2!$E3:$E65535)*(Sheet2!$H3:$H65535=$A$2)*(Sheet2!$I3:$I65535=A7)*(Sheet2!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet2!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))
SUM((Sheet3!$E3:$E65535)*(Sheet3!$H3:$H65535=$A$2)*(Sheet3!$I3:$I65535=A7)*(Sheet3!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet3!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))
)
I will need to expand this formula over time as more sheets are added. Also the names of the sheets will not be Sheet1, Sheet2, etc. and will not follow any sort of pattern. Also the formula will be used in each cell of a large table with variations in the DATE ranges.
Is there any way to take the base equation, some sort of FOR loop, and a list of the sheet names to make the implementation easier? That way when a new sheet is added all I have to do is add it to the list of sheets and all of the cells will update their formulas automatically?