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

Tatiana_k

New Member
Joined
Sep 16, 2014
Messages
23
Office Version
  1. 2010
Platform
  1. Windows
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
 
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
Excel Formula:
=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).
Excel Formula:
=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"))
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
first test, got 4 with the semicolons between names

View attachment 49783

12 with the second

1635175482839.png


1635175551105.png
 

Attachments

  • 1635175252452.png
    1635175252452.png
    5.5 KB · Views: 9
Upvote 0
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).
 
Upvote 0
I've changed the regional setting. first test result 4

1635176984396.png

second test got 12
1635177329067.png


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

in my formula I've got 17

1635177960413.png
 
Upvote 0
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.
 
Upvote 0
=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.
 
Upvote 0
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))
 
Upvote 0
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top