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]

Tatiana_k,

I'm not sure if I've followed correctly but it appears you want to populate column C with column P and same row but across a number of worksheets which are numbered 1, 2, 3, etc.
You have found =SUM('1:10'!P7) works for C7 and when copied down, but you want to have that end sheet 10 as a variable specified in E4.

For test data I've just populated all the numeric sheet names column P with its row number and =SUM('1:10'!P7) works.

If I want to limit the sheets summed then doesn't this work?

=SUMPRODUCT(SUMIF(INDIRECT("'*"&ROW(INDIRECT("1:"&$E$4))&"'!P"&ROW()),"<> "))

Tatiana_k-2.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3
4					7
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	0	0	49	168	0	0.70833	0	0	119
8	0	0	56	168	0	0.66667	0	0	112
9	0	0	63	168	0	0.625	0	0	105
10	0	0	70	168	0	0.58333	0	0	98
11	0	0	77	168	0	0.54167	0	0	91
12	0	0	84	168	0	0.5	0	0	84
13	0	0	91	168	0	0.45833	0	0	77
14	0	0	98	168	0	0.41667	0	0	70
15	0	0	105	168	0	0.375	0	0	63
16	0	0	112	168	0	0.33333	0	0	56
17	0	0	119	168	0	0.29167	0	0	49
18	0	0	126	168	0	0.25	0	0	42
Summary

Cell Formulas
Range	Formula
A7:B18	A7	='10'!A7
C7:C18	C7	=SUMPRODUCT(SUMIF(INDIRECT("'*"&ROW(INDIRECT("1:"&$E$4))&"'!P"&ROW()),"<> "))
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

 

[Tatiana_k]

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.

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.

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.

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.

“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.”
Right, If I put 1; it doesn’t work because first sheet’s name is not numeric.

I think the problem is in the syntax.It' hardly asking too much. As you can see the formula works without list. I just don’t know how you can skip the first sheets with the different name.
I use to do it in VBA, but they I asked me to do it without macro.

 

[jasonb75]

I think the problem is in the syntax.It' hardly asking too much.

The problem is you not listening to advice because the advice given doesn't comply with your exact requirements.

You can NOT refer to a sheet by position, you can ONLY refer to it by name. If that name is not a number then you need to use the actual name instead of INDIRECT(ROW( to create an array. The only way to refer to those sheets by name in this type of formula is to use a list. You could try adding that list directly to the formula using an array constant which may or may not work, but however you do it there needs to be a list somewhere.

It is plain and simple, not rocket science. If you don't want to take my word for it then there are consultants out there who do this for a living and will quite happily charge you upwards of $200 / hr to tell you the same thing.

 

[Tatiana_k]

The problem is you not listening to advice because the advice given doesn't comply with your exact requirements.

You can NOT refer to a sheet by position, you can ONLY refer to it by name. If that name is not a number then you need to use the actual name instead of INDIRECT(ROW( to create an array. The only way to refer to those sheets by name in this type of formula is to use a list. You could try adding that list directly to the formula using an array constant which may or may not work, but however you do it there needs to be a list somewhere.

It is plain and simple, not rocket science. If you don't want to take my word for it then there are consultants out there who do this for a living and will quite happily charge you upwards of $200 / hr to tell you the same thing.

The problem is you not listening to advice because the advice given doesn't comply with your exact requirements.

You can NOT refer to a sheet by position, you can ONLY refer to it by name. If that name is not a number then you need to use the actual name instead of INDIRECT(ROW( to create an array. The only way to refer to those sheets by name in this type of formula is to use a list. You could try adding that list directly to the formula using an array constant which may or may not work, but however you do it there needs to be a list somewhere.

It is plain and simple, not rocket science. If you don't want to take my word for it then there are consultants out there who do this for a living and will quite happily charge you upwards of $200 / hr to tell you the same thing.

Ok, thank you.

However, in my formula I refer to sheet position and not to name and it works. I will dig fewer and let you know. I’m consultant myself, but in VBA. It’s my first experience in the MS Excel.

 

[jasonb75]

in my formula I refer to sheet position and not to name and it works.

No, you don't. You might think that it is what you have done but you can NOT refer to a sheet by position in any formula. That can only be done with vba.

 

[jasonb75]

Tried editing my last reply but reached the time limit before I could save it.

Repeating what I said earlier, it 'might' work with an array constant. Common practice is to use a list of sheet names in a sheet somewhere, as you are unwilling to do that we venturing into unknown territory in order to accommodate your eccentricities.

Note that this is for 3 sheets not in the numeric sequence, {"Data","Summary","Weekly"} edit as needed.

You will see +3 in 2 places in the formula, if there are more or less than 3 sheets not in the numeric sequence then these points will need to be changed to reflect the correct number.
If it returns an error then try changing the commas in the array of sheet names to semicolons.

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

If it doesn't work this way then it will not work at all!

 

[Tatiana_k]

Tried editing my last reply but reached the time limit before I could save it.

Repeating what I said earlier, it 'might' work with an array constant. Common practice is to use a list of sheet names in a sheet somewhere, as you are unwilling to do that we venturing into unknown territory in order to accommodate your eccentricities.

Note that this is for 3 sheets not in the numeric sequence, {"Data","Summary","Weekly"} edit as needed.

You will see +3 in 2 places in the formula, if there are more or less than 3 sheets not in the numeric sequence then these points will need to be changed to reflect the correct number.
If it returns an error then try changing the commas in the array of sheet names to semicolons.

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

If it doesn't work this way then it will not work at all!

Thanks again Jason. I've tried your formula. It return the value 4 in the cell A4. It only takes the value from the "1" sheet and doesn't sum the values from other numeric sheets. I tried all combination.
The values in the numeric sheets are as follows :
1 sheet -4
2 sheet -1
3 sheet-2
4 sheet -5
5 sheet -3
6 sheet -2

My formula return the value 10 in the cell A2. It summarises the values from 4 to 6 sheets.

 

[jasonb75]

Why have you changed +3 to +4 when there are only 3 named sheets?

You will see +3 in 2 places in the formula, if there are more or less than 3 sheets not in the numeric sequence then these points will need to be changed to reflect the correct number.

Once you have corrected that, try array confirming the formula with Ctrl Shift Enter.

 

[Tatiana_k]

Why have you changed +3 to +4 when there are only 3 named sheets?

Once you have corrected that, try array confirming the formula with Ctrl Shift Enter.

I've got the same result. As I said I've tried all combination with your formula, changing also the cell E1 to F1 etc.., stil the result 4.

 

[jasonb75]

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.