INDIRECT SUM WITH SHEET NAMES

O

occy

New Member
Joined
Jul 15, 2003
Messages
13
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
occy said:
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
Click to expand...

You can take a different (faster) route... Insert 2 blank worksheets, name them First and Last, respectively. Place all the relevant worksheets with data between First and Last and use a 3D formula...

=SUM(First:Last!J60)
 
Upvote 0
Hey Juan

Wow, thanks for the lightning fast response. There are spaces in the sheet names, how do I compensate for that? Aladin's quick solution of using First and Last sheets will work but I would like to try for something more elegant if possible! If indirect won't work, how do you suggest I acheive my objective?

I found this on j-walk but it also gives me the #ref! error.

WORKSHEET FORMULA TIP

By George Simms

This array formula returns the sum of cells in the 3D range bounded by the
by the sheets named in cells B2 and B3.

=SUM(N(INDIRECT(ROW(INDIRECT(B2&":"&B3))&"!A1")))

Thanks
 
Upvote 0
That formula seems to work when the sheets are named

1
2
3
etc.

but it's not a true 3D formula because if for example if I have this values:

1!A1 = 1
2!A1 = 2
3!A1 = 3

and they are ordered like

1, 3, 2

then this

=SUM(1:3!A1)

should return 4.

The formula creates an array of numbers from 1 to 3 ({1, 2, 3}) and then that is passed to the INDIRECT function, like this:

{"1!A1","2!A1","3!A1"}

giving in the end a result of 6. I guess Aladin's solution is the easiest... I'll keep looking for a more comprehensive solution tough...
 
Upvote 0
Ok, I think I have it working...

First, we need to define a name using Insert | Name, Define

Sheets

which refers to:

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

Now.... I have a workbook with 7 sheets, like this:

"Hoja1","Hoja3","Hoja7","Hoja6","Hoja5","Hoja4","Hoja2"

I disorganized them in purpose !

All have in A1, 1, which I used for testing.
Libro1
ABCDE
11SheetsHoja1
2
3FromSheetHoja3
4ToSheetHoja2
5whichcellA1
6
7TheSum6
Hoja1


The array formula in B7 is

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

which could be simplified to

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

if in C3 you would put

=MATCH(B3,Sheets,0)

and in C4:

=MATCH(B4,Sheets,0)

For some reason that I'm still investigating, it won't produce the correct result if we try to sum, say A1:B1... if anyone has any insight on this, I'd appreaciate it.
 
Upvote 0
Juan Pablo González said:
Ok, I think I have it working...

First, we need to define a name using Insert | Name, Define

Sheets

which refers to:

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

Now.... I have a workbook with 7 sheets, like this:

"Hoja1","Hoja3","Hoja7","Hoja6","Hoja5","Hoja4","Hoja2"

I disorganized them in purpose !

All have in A1, 1, which I used for testing.

...

The array formula in B7 is

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

which could be simplified to

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

if in C3 you would put

=MATCH(B3,Sheets,0)

and in C4:

=MATCH(B4,Sheets,0)

For some reason that I'm still investigating, it won't produce the correct result if we try to sum, say A1:B1... if anyone has any insight on this, I'd appreaciate it.
Click to expand...

Try...

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

You have lost me. I don't understand the reason for the "sheets" name. What does that do? I copied your method and now get a result (which is progress!) but it is 0 which is not the correct result. I have over 80 sheets, and there are spaces in the sheet names.

Thanks
 
Upvote 0
occy said:
Guys

You have lost me. I don't understand the reason for the "sheets" name. What does that do? I copied your method and now get a result (which is progress!) but it is 0 which is not the correct result. I have over 80 sheets, and there are spaces in the sheet names.

Thanks
Click to expand...
The GET.WORKBOOK is an old macro formula, an XLM formula. The parameter that you see, the 1 or the 16 is to define what type of information does it return.

The 1 returns an array of all the sheets in the workbook, but including the name of the workbook. The 16 returns the name of the workbook. Then, using the SUBSTITUTE function, Sheets returns an array of only the sheet names.

The array that it created is in E1.

As for the spaces, it shouldn't be a problem, look at this:
Sum 3D reference with Indirect.xls
ABCDE
112SheetsHoja1
2
3FromSheetHoja11=MATCH(B3,Sheets,0)
4ToSheetHoja27=MATCH(B4,Sheets,0)
5whichcellA1:B1
6
7TheSum7
87
921
Hoja 1


The formula in B9 is the version that Aladin proposed, that solves the issue that I couldn't !! thanks Aladin for that.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Sumifs with nested Xlookup-- Adding Indirect to perform calculation across multiple sheets
Replies
1
Views
906
Eric W
Eric W
P
  • Solved
Dynamic Sheet Tabs in Index Match function
Replies
9
Views
293
Peter_SSs
Peter_SSs
R
  • Question Question
Help to sum all cells above until blank cell
Replies
3
Views
694
RemyMoo
R
P
  • Question Question
Worksheet and cell value matched to range
Replies
1
Views
509
Farscape2020
F
S
  • Solved
Indirect Sum across Sheet Range
Replies
3
Views
419
Fluff
Fluff

Forum statistics

Threads
1,224,808
Messages
6,181,073
Members
453,020
Latest member
mattg2448

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top