• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

How many sheets in this range

Hi,

See formula below - which covers a group of excel sheets.

=SUM('Sheet1:Sheet20'!B12)

This add up the value in B12 from 20 different sheets (approx).  However, I may add or delete sheets.

QUESTION: I want another formula which COUNTS the number of sheets in this range.

How do I write a formula that simply tells me how many sheets are in the range - as it wont always be 20.
0
Patrick O'Dea
Asked:
Patrick O'Dea
3 Solutions
 
JorgenCommented:
Created a named formula and use an old XLM 4 command =GET.WORKBOOK(4)
0
 
JorgenCommented:
To be a little more specific.

By creating a named formula go to Formulas -> Name Manager -> Type a name e.g. NumSheets and in the refers to type in =GET.WORKBOOK(4)

Then you can concatenate you sum formula to include the first sheet up until the number specified.

If you need help with the concatenation - please let me know.

regards

Jørgen
0
 
Patrick O'DeaAuthor Commented:
Thanks Jørgen ,

I don't follow you explanation ....

Any chance you could attach a very simple example.

Thanks again,
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Rob HensonFinance AnalystCommented:
You can use the COUNTA function to tell you how many cells the sum is using:

=COUNTA('Sheet1:Sheet20'!B12)

Doesn't necessarily tell you how many sheets but does tell you how many of the sheets in that range contain something.

Thanks
Rob H
0
 
nutschCommented:
Details on Jorgen's point:

on your workshoot, do Ctrl+F3 to access name manager, click New, put Name = CountSheets, in RefersTo, put =GET.WORKBOOK(4)

OK, CLose

in your worksheet, you can now use =CountSheets and it will give you the number of worksheets in the workbok.

Warning: it's the total number of worksheets in the workbook, including some that are not in your range, and any hidden sheets too.

Thomas
0
 
JorgenCommented:
Dewsbury

Thomas is correct about the issues, that he states regarding hidden workbooks, as well as the issue about including counting workbooks that is included for other purposes.

I also did a little more research on concatenating to do 3D sum calculation, and it is actually more complex than I thought I have created beforehand. If that is the solution, you want, I will try to guide you, but I might have a much simpler solution for you. This solution has worked for one of my clients for years.

At my clients side we created a copy of the other sheets on our summary sheet, - that was always placed as the last sheet. We did not put in any figures, but that meant you would always know your first and your last sheetname

I have included an example - and as you can see in ark4 (sheet4) I have hidden column A, and then summarises in column B. But I sum all cell A1 in cell B1 and thereby I always know the first name and the last name of the sheets.

If you do not control which sheet is the last one - we can do that in VBA.

Reconsidering your question - I believe that the simpler solution will be easier to understand for somebody taking over your job the day you want to move on.

regards

Jørgen
Sum-Sheets.xlsx
0
 
Patrick O'DeaAuthor Commented:
Thanks all for your help.  Rob Hensons solution worked best for me.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now