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]

As far as I can see you haven't array confirmed it as previously suggested.

Click on the cell, press the f2 key, then press shift ctrl and enter together (in the same way that you would use ctrl alt delete to open task manager).

I tested it in office 365 prior to posting the formula and it worked correctly. If it does not work for you when it is entered correctly then that means that it will not work with your version of excel without using some brute force methods (that may or may not work) that I will not be able to help you with as I do not have the same version of excel to test them on.

That leaves you with the options that I previously stated which you have dismissed as not suitable.

Great, I've got 12 instead of 17. It means we summarise 4 sheets. And what number have you got?

 

[jasonb75]

I don't remember the exact number but it was correct for the test that I did yesterday.

Can you identify from the result which sheets are being summed in the formula? If it is not obvious, try changing the value in 1 of the sheets then see if the result has changed or not, repeat for each sheet until you can see which are included and which are missing.

 

[Tatiana_k]

I don't remember the exact number but it was correct for the test that I did yesterday.

Can you identify from the result which sheets are being summed in the formula? If it is not obvious, try changing the value in 1 of the sheets then see if the result has changed or not, repeat for each sheet until you can see which are included and which are missing.

The sheets 1,2,3,4 have been summed.

 

[jasonb75]

That means that your version of excel doesn't accept the index array method used to add the named sheets to the formula, it may work using the brute force methods but I have no way of testing them so it's going to be all luck and no judgement.

Try this version first.

Remember that every time you change the formula, you must array confirm it again with Ctrl Shift Enter

=SUMPRODUCT(SUMIF(INDIRECT(T(IF({1},"'"&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]

That means that your version of excel doesn't accept the index array method used to add the named sheets to the formula, it may work using the brute force methods but I have no way of testing them so it's going to be all luck and no judgement.

Try this version first.

Remember that every time you change the formula, you must array confirm it again with Ctrl Shift Enter

=SUMPRODUCT(SUMIF(INDIRECT(T(IF({1},"'"&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"))

I've got 12 again. It just summurised the value 4 from "1" sheet 3 times.

If I add another sheet 1 before numeric sheets I've got 16 (4 times the value 4 from "1" sheet.)

If I add on more sheet. i've got 20. It means the formula summarises value 4 from the "1" sheets 5 times.

In my formula it also takes in account the number of "string" sheets and summarises accordingly the last 3 sheets, then last 2 sheets.

 

[jasonb75]

With A6 selected (as in your last screen capture) Type Alt m v (one after the other, not together).
You should see a pop up window. Press e 20 times, then screen capture the popup.

 

[Tatiana_k]

With A6 selected (as in your last screen capture) Type Alt m v (one after the other, not together).
You should see a pop up window. Press e 20 times, then screen capture the popup.

Fantastic debugger!

 

[jasonb75]

That's exactly what I needed, now I can compare that to what is happening in mine and try to figure out the difference.

This could take a while, you've got time to go for a coffee, 6 course meal, round the world cruise, vacation on mars, etc. While you wait.

 

[Tatiana_k]

That's exactly what I needed, now I can compare that to what is happening in mine and try to figure out the difference.

This could take a while, you've got time to go for a coffee, 6 course meal, round the world cruise, vacation on mars, etc. While you wait.

 

[Tatiana_k]

Thanks a lot, really appreciated your help and patience.

I’ll sleep less idiot tonight (if it’s possible).