INDIRECT SUM WITH SHEET NAMES

[occy]

I am trying to sum a particular cell across a range of worksheets. My formula is:

=SUM(INDIRECT(K4&":"&K5&"!"&"J60"))

Where K4 contains the name of the first worksheet and K5 contains the name of the last worksheet in the range I want to sum. J60 is the cell I want to sum.

I get the dreaded #ref! result. Help would be much appreciated.

Thanks

 

[Aladin Akyurek]

i need the dynamic cell, i want to copy down and across

And you refuse:

{=AVERAGE(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(1,INDIRECT("'"&Sheets&"'!"&CELL("address",C5)))))}

Why?

 

[Ver101]

And you refuse:

{=AVERAGE(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(1,INDIRECT("'"&Sheets&"'!"&CELL("address",C5)))))}

Why?

Sorry i tried the formula, it is not working. getting 0 value

 

[Aladin Akyurek]

Sorry i tried the formula, it is not working. getting 0 value

You are saying that:

{=AVERAGE(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(1,INDIRECT("'"&Sheets&"'!C5))))}

works, but

{=AVERAGE(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(1,INDIRECT("'"&Sheets&"'!"&CELL("address",C5)))))}

does not while it should when copied down/across?

 

[Ver101]

You are saying that:

{=AVERAGE(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(1,INDIRECT("'"&Sheets&"'!C5))))}

works, but

{=AVERAGE(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(1,INDIRECT("'"&Sheets&"'!"&CELL("address",C5)))))}

does not while it should when copied down/across?

SUBTOTAL(1,INDIRECT("'"&Sheets&"'!C5) it does work in c5 provided it contains the the cell of going to sum up in different sheet and while the second one it returns #DIV /0

 

[Ver101]

SUBTOTAL(1,INDIRECT("'"&Sheets&"'!C5) it does work in c5 provided it contains the the cell of going to sum up in different sheet and while the second one it returns #DIV /0

cell c5 should be the cell reference in which the amount in c5 in various sheet should be total not the reference cell such as A1 as indirect in cell c5

 

[Ver101]

in the second with address c5 if i highlight the formula and click F9 it shows the total but if i control shift enter it show 0 amount

 

[Ver101]

if i evaluate the formula its say sum(false,12,12,false,false) that's why it becomes zero but using f9 computes 24

 

[Aladin Akyurek]

if i evaluate the formula its say sum(false,12,12,false,false) that's why it becomes zero but using f9 computes 24

Just to make sure:

Are we evaluating

{=AVERAGE(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),IFERROR(SUBTOTAL(1,INDIRECT("'"&Sheets&"'!"&$C$5)),"")))}

where C5 houses a range specification like A1:B1?

 

[Ver101]

Just to make sure:

Are we evaluating

{=AVERAGE(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),IFERROR(SUBTOTAL(1,INDIRECT("'"&Sheets&"'!"&$C$5)),"")))}

where C5 houses a range specification like A1:B1?

Nope. C5 houses the value not the range

 

[Aladin Akyurek]

Nope. C5 houses the value not the range

What is the full set of sheets that you have?