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

 

[bshaggy6]

It may be that the values in cells G12 and H12 don't exactly match the sheet names that you want to pick values from? Other than that I don't have any ideas sorry.

the value in cell G12 is 1 in H12 it is 5. I have sheets labeled from 1-31 exactly how it is in cell g12 and h12.

 

[bshaggy6]

I have figured out why it isnt working. The values in cell g12 and h12 represent dates. I reformatted the cells to just have the number 1 if it was for example 06/01/2015 and 5 if it really was 06/05/2015. Since these cells represent dates and not the actual label of the worksheets is there a way to fix this? thanks in advance for any help/

 

[Ver101]

Try...

=SUM(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(9,INDIRECT("'"&Sheets&"'!"&$B$5))))

i am checking on this formula, i tried replacing subtotal 9 into subtotal 101 for the average but still computing the total is there a way i can compute the average amount?

 

[Ver101]

i am checking on this formula, i tried replacing subtotal 9 into subtotal 101 for the average but still computing the total is there a way i can compute the average amount?

Nevermind on this, already solved.

 

[Aladin Akyurek]

Nevermind on this, already solved.

What modification did you run?

 

[Ver101]

change the sum to average, by the way i am trying to use the formula and making the sum value to dynamic as:

AVERAGE(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(101,INDIRECT("'"&Sheets&"'!"&COLUMN()&ROW()))))

can you help me on this?

 

[Aladin Akyurek]

change the sum to average,

Right.

by the way i am trying to use the formula and making the sum value to dynamic as:

AVERAGE(IF((MATCH(Sheets,Sheets,0)>=$C$3)*(MATCH(Sheets,Sheets,0)<=$C$4),SUBTOTAL(101,INDIRECT("'"&Sheets&"'!"&COLUMN()&ROW()))))

can you help me on this?

You want to be able to copy down the formula, right?

Try something like...Control+shift+enter:

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

 

[Ver101]

Right.



You want to be able to copy down the formula, right?

Try something like...Control+shift+enter:

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

not necessarily, i want to sum up or average the same cell where the formula sits, if my formula is on c5 it will compute all amount in c5 in various sheets

 

[Aladin Akyurek]

not necessarily, i want to sum up or average the same cell where the formula sits, if my formula is on c5 it will compute all amount in c5 in various sheets

What do you want then since

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

apparently does not suffice?

 

[Ver101]

What do you want then since

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

apparently does not suffice?

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