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

 

[Ver101]

What is the full set of sheets that you have?

Same sheet as your example but instead of housing the range i need the range included in the formula because i need a summary totals

 

[Aladin Akyurek]

Same sheet as your example but instead of housing the range i need the range included in the formula because i need a summary totals

Summary totals? I thought you were after averages...

And care to note the post number you are referring to?

 

[Ver101]

Summary totals? I thought you were after averages...

And care to note the post number you are referring to?

Combination of sum and average in the summary sheet

 

[Ver101]

hi got already the right formula but i am getting a circular reference is there a way i can exclude the summary sheet from the other sheet to be totalled? by the way it is the formula i got

=SUM(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),N(INDIRECT("'"&Sheets&"'!"&ADDRESS(ROW()-7,COLUMN()-1)))))