Formula To Count The Number Of Sheets In The Excel Workbook (Version 2003 And Below)

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.