In fact, MS Excel doesn’t have any Straight Formula to Count the Number of Sheets in the Workbook. But, we have Indirect Way to Count the same.
HOW?
First we need to “Define Name” by Referring to =GET.WORKBOOK(1)&T(NOW())
Then, Use the below Formula to Count the Number of Sheets
=COUNTA(INDEX(DEFINED NAME,0))
Step By Step?
2003 and Below excel versions
1. Click ”Ctrl+F3′, then ‘Define Name’ Box appears
2. Enter ‘Names in workbook’ as CountSheets (Your choice)
3. Enter ‘Refers To’ as =GET.WORKBOOK(1)&T(NOW())
4. Click OK
5. Come to the Cell, where you want to enter the Formula
6. Enter the Formula as =COUNTA(INDEX(CountSheets,0))
7. Click Enter
2007 and above Excel Versions
1. Click ‘Ctrl+F3’, then ‘Name Manager’ Box Appears
2. Click ‘New’ (use shortcut key Alt+N)
3. Then, ‘New name’ box appears
4. Enter ‘Name’ as “CountSheets“(Your choice)
5. Enter ‘Refer To’ as =GET.WORKBOOK(1)&T(NOW())
6. Click OK
7. It takes you to the ‘Name Manager’ box again (you can find the Defined Name in the list)
8. Click ‘Close’
9. Come to the Cell, where you want to enter the Formula
10. Enter the Formula as =COUNTA(INDEX(CountSheets,0))
11. Click Enter
If you google on this, you can find alternative solutions with VBA code. Without using VBA, I think this is the easy way to Count Number of Sheets.
Don’t forget to give comment below, if you find any alternative solution, or if you have any questions.
Also read
- How to Count Number of Rows in a Word Table?
- Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel
Courtesy: lostinexcel.blogspot.in
Very Good- I was searching for long, quite simple formula
Thank you!!!!
Perfect!!! Thank you very much. I was searching for a long time and now – with very simple formulas – I can counting all sheets.
the formula gives the count of total number of sheets. How do I change the formula to count ONLY the number of sheets to the left/right?
You can make it a lot more simple:
For the defined name >> CountSheets >> =Get.Workbook(4)
For the worksheet function >> =Index(CountSheets,0)
That’s All
Thanks a lot. I wonder why it is not a part of MS Excel? Or is it?
Realy works.
Thanks.
Excellent solution