SUM a same cell in the RANGE of sheets using SUMPRODUCT(SUMIF(INDIRECT

[Tatiana_k]

Hi guys,

I need to sum all A1 across many sheets with daily data and then put the result in summary sheet and apply this formula to 365 rows in the column.

The formula works if the name of sheets in sequence is number. Number of the sheets with Data set in F1

=SUMPRODUCT(SUMIF(INDIRECT("'*"&ROW(INDIRECT("1:"&F1))&"'!A1"),"<>0"))

But my first sheet is called Summary, second sheet is called Data and the formula stops working.

Would anyone have a tip on how to solve this issue?

Thanks in advance

 

[Tatiana_k]

You can not have names in there for sheets that do not yet exist.

You could try error trapping, it looks like it works but so did the earlier formulas.

=SUMPRODUCT(IFERROR(SUMIF(INDIRECT("'"&IF(ROW(INDIRECT("1:"&$E$1+3))>$E$1,INDEX({"Data","Suary","Weekly"},N(IF({1},ROW(INDIRECT("1:"&$E$1+3))-$E$1))),ROW(INDIRECT("1:"&$E$1)))&"'!A1"),"<>0"),0))

Yes, it works now. You are the best! Thank you very much.