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

 

[Toadstool]

Hi Tatiana_k,

If sheets are contiguous then you can just use SUM

=SUM('Sheet 2:Sheet 4'!A1)

 

[Tatiana_k]

Hi Tatiana_k,

If sheets are contiguous then you can just use SUM

=SUM('Sheet 2:Sheet 4'!A1)

View attachment 49666

Thank you very much. Actually I use this formula right now.

But I’d like to Autofill my column “C” changing the number of the sheets in the cell “E4” every week.

I’m doing this in the column “E”, but I’ can’t find the solution for the sum in the column “C”.

I know it’s much easier to do it in VBA, but right now I need to use a formula.

Weekly Equipment status report .xlsx
	A	B	C	D	E	F	G	H	I
4					21
5	Equipment	Serial Number	B/D Hours	Target Hours	Running Hours	Availability, %	DOH vsTarget hrs	Estimated Utilisation, %	Est S/B Hours
6	HAULING MINING RDT's (773)
7	773-07	PRB00421	77.42	504	154	85%	30.6%	36.2%	272.18
8	773-08	PRB00527	240.00	504	0	52%	0.0%	0.0%	264.00
9	773-09	PRB00502	240.00	504	0	52%	0.1%	0.1%	263.70
10	773-10	PRB00524	20.25	504	334	96%	66.3%	69.0%	149.75
11	773-11	PRB00525	38.92	504	146	92%	29.0%	31.5%	318.78
12	773-12 .	PRB00526	11.83	504	336	98%	66.6%	68.2%	156.67
13	773-13	PRB00527	2.75	504	353	99%	69.9%	70.3%	148.75
14	773-14	PRB00528	1.58	504	356	100%	70.6%	70.8%	146.82
15	773-15	PRB00529	66.33	504	254	87%	50.4%	58.0%	183.67
16	773-16	PRB00530	64.00	504	81	87%	16.1%	18.4%	358.90
17	773-17	PRB00531	0.42	504	356	100%	70.7%	70.8%	147.28
18	773-18	PRB00532	45.42	504	286	91%	56.7%	62.4%	172.58
19
Summary

Cell Formulas
Range	Formula
A7:B18	A7	='10'!A7
C7:C18	C7	=SUM('1:10'!P7)
D7:D18	D7	=$E$4*24
E7:E18	E7	=INDIRECT(E$4&"!"&CELL("address",D7))-'1'!C7
F7:F18	F7	=(D7-C7)/D7
G7:G18	G7	=E7/D7
H7:H18	H7	=IFERROR(E7/(D7-C7),"")
I7:I18	I7	=D7-C7-E7

 

[jasonb75]

You would need to create a list of sheet names, then refer to that list with the formula. With the list of sheet names in a sheet called "list", starting in A2 something like this should hopefully do it, although it's a bit beyond the tried methods so no guarantees.

=SUMPRODUCT(SUMIF(INDIRECT("'"&OFFSET(List!$A$2,0,0,$E$1,1)&"'!A1"),"<>0"))

 

[Tatiana_k]

You would need to create a list of sheet names, then refer to that list with the formula. With the list of sheet names in a sheet called "list", starting in A2 something like this should hopefully do it, although it's a bit beyond the tried methods so no guarantees.

=SUMPRODUCT(SUMIF(INDIRECT("'"&OFFSET(List!$A$2,0,0,$E$1,1)&"'!A1"),"<>0"))

Thank you, but I'd like to avoid creating the list. In this case I need to change the end of the list every day and copy-paste the result in the column C, as I’m doing now. =SUM('1:10'!P7); It’s very time consuming operation. I’ve managed to atomise this operation for the simple calculation in the column E; =INDIRECT(E$4&"!"&CELL("address",D7))-'1'!C7 .

Now I change the number in the E4 (for example with the 21 sheets) and this updates the column E with the date from the 21 sheets. I produced the formula for the SUM. =SUMPRODUCT(SUMIF(INDIRECT("'*"&ROW(INDIRECT("1:"&E4))&"'!A1"),"<>0")). It’s working well if the name of sheets in sequence is number and there is no the sheets before. Unfortunately, I have “summury, data, weeks etc...” sheets before my number sheets started.

 

[jasonb75]

Have you tried the hidden sheets method?

Hide a sheet called 'First' immediately before the first sheet that should be included in the formula.
Hide a sheet called 'Last' immediately after the last sheet that should be included in the formula. Note that both of these sheets should be completely empty.
Change your formula to

=SUM(First:Last!A1)

(change cell as needed for first one then fill down).

Any new sheets added to the workbook between the hidden 'First' and 'Last' sheets will automatically be included in the formula without needing to edit.

If that will not work for you then you will either need to use the list method that I suggested earlier, use the simple formula method that you have to edit each time to include the new sheets, or use vba instead.

I'm not convinced that it will be possible to do it the way that you want to with the newer functions in the latest versions of excel, with something as old as 2010 you are asking the impossible.

 

[jasonb75]

Following up, I might have found a way that looks like it should never work but appears to. Note that this was done in office 365 which on occasion does behave slightly differently to older versions for no apparent reason so it may still not work for you.

Note that the formula requires a list of the names of the sheets that are not sequential numbers (summary, data, etc.) if they are to be summed in the formula. The range $F$1:$F$3 in the formula refers to this list.

=SUMPRODUCT(SUMIF(INDIRECT("'"&IF(ROW(INDIRECT("1:"&$E$1+COUNTA($F$1:$F$3)))>$E$1,INDEX($F$1:$F$3,ROW(INDIRECT("1:"&$E$1+COUNTA($F$1:$F$3)))-$E$1),ROW(INDIRECT("1:"&$E$1)))&"'!A1"),"<>0"))

 

[Tatiana_k]

Following up, I might have found a way that looks like it should never work but appears to. Note that this was done in office 365 which on occasion does behave slightly differently to older versions for no apparent reason so it may still not work for you.

Note that the formula requires a list of the names of the sheets that are not sequential numbers (summary, data, etc.) if they are to be summed in the formula. The range $F$1:$F$3 in the formula refers to this list.

=SUMPRODUCT(SUMIF(INDIRECT("'"&IF(ROW(INDIRECT("1:"&$E$1+COUNTA($F$1:$F$3)))>$E$1,INDEX($F$1:$F$3,ROW(INDIRECT("1:"&$E$1+COUNTA($F$1:$F$3)))-$E$1),ROW(INDIRECT("1:"&$E$1)))&"'!A1"),"<>0"))

 

[Tatiana_k]

Thank you very much..

My sequence of sheets remains constant and the names as well. I need to summarise for ex. 1, 2 3 4 5… to 10 sheets, sometimes 1 2 3 4 …. 15. And so on; I know that with the list it’d work perfectly.

But I need do it without list. I need to use once my formula and then copy-paste through the whole column. Next time than I want to change my number of sheets in the formula, I’ve just change the number in the cell E4 and all value in the column should change automatically, as in the column E.

I tested my formula for Sum with the sequence of sheets with the numeric names. It works well.

Now I have 3 first sheets: “Summary, Data, Week” , then 1 2 3 ….31 and I say to star from 4rd sheet to the number in E4(in our case value 21.

=SUMPRODUCT(SUMIF(INDIRECT("'*"&ROW(INDIRECT("4:"&E4))&"'!A1"),"<>0")).

Formula works, but it summarises from 4rd numeric sheet 4,5,6,7 ….21 instead of 1, 2,3,4,5 …21

 

[jasonb75]

Formula works, but it summarises from 4rd numeric sheet 4,5,6,7 ….21 instead of 1, 2,3,4,5 …21

That's because you've told the formula to do that. with "4:" if you use "1:" then it will go from the first sheet.

But I need do it without list.

It is not suddenly going to become possible because it is what you need. As I said above, you will need a list for the sheets that are not in a numeric sequence. Without a list of those sheet names you are limited to the options in post 6.