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

 

[jasonb75]

If you look at the underscored section of the formula in the screen cap of the debugger you will see that there are commas and semicolons between the names, the correct formula will only have semicolons. In short, commas separate columnsof the array, semicolons separate rows. For it to work correctly it needs to produce the array as a single column. The latest versions of excel with dynamic array functionality do this easily, older versions (2019 or earlier) need a lot of persuasion to do this.

Same test again with the evaluation tool (aka debugger), see if either of these work correctly.
Press e 22 times for this one

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

Press e 20 times for this one (this i know will not work, it's a test for evaluation purposes).

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

 

[Tatiana_k]

first test, got 4 with the semicolons between names

View attachment 49783

12 with the second

 

[jasonb75]

Could you post the formula exactly as you have it in your sheet please. Obviously you've had to change the sheet names, but is there anything else that you've needed to change for regional settings etc? The only reason I can think of for the differences that I'm seeing between your evaluations and mine would be that you're in a European country that uses a comma instead of a period as a decimal separator.

The first screen capture above looks like it should work, are you sure that you're adding the correct values manually (the test formula is adding A1 in each sheet).

 

[Tatiana_k]

I've changed the regional setting. first test result 4

second test got 12

Correct number is 17=4+1+2+3+5+2

in my formula I've got 17

 

[jasonb75]

Please post the formula used for the first evaluation shown above (refernce Summary!$A$7).

Also, what number is in F1 (or E1 whichever the formula refers to). It appears that the sheet count has moved between E4 and F1 in your examples, I've somehow managed to mix that up and use E1 so this small inconsistency may be the root of the errors.

 

[Tatiana_k]

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

For your formula the number in E1=1 in F1=6

For my formula I use only F1=6. My intial target - to change only one number to to update a whole column data.

 

[jasonb75]

So try changing E1 to 6, do you get 17 then?

 

[Tatiana_k]

So try changing E1 to 6, do you get 17 then?

Yes! Superbe, thanks a lot.
I'll try to put it in my real workbook now.

 

[Tatiana_k]

So try changing E1 to 6, do you get 17 then?

Just last comment. In the real workbook, some of my "text sheets" are not empty and it gives me an error.

 

[jasonb75]

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))