Saturday, December 31, 2011

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.

54 comments:

  1. This was exactly what I was looking for. Thank you. It works great.

    ReplyDelete
  2. It works fine, but in Excel 2010
    Formula is =COUNTA(INDEX(CountSheets;0))
    Thanks

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Interesting. In Excel 2013, the recommended formula works:
      =COUNTA(INDEX(CountSheets,0))

      Delete
  3. thank so much

    It's what I was looking for

    ReplyDelete
  4. Absolutely brilliant! thanks

    ReplyDelete
  5. Its a very easy and a brilliant option.... thank you....

    ReplyDelete
  6. Thanks for the formula. It works great.

    However, when I close and open the file, the formula does not calculate correctly. To get the correct figure I have to click Ctrl F3 and select the CountSheet option every time I open the file. Is there a way to ensure the formula is permanent?

    ReplyDelete
    Replies
    1. You have to save it as a macro-enabled workbook!

      Delete
  7. Awesome. Thanks for the help.

    ReplyDelete
  8. Even easier way of doing it. Just insert a new sheet at the beginning and it will number it for you.

    ReplyDelete
    Replies
    1. Not if you have renamed the sheets.

      Delete
  9. 1. Click ''Ctrl+F3', then 'Define Name' Box appears
    2. Enter 'Names in workbook' as CountSheets (Your choice)
    3. Enter 'Refers To' as =COUNTA(INDEX(GET.WORKBOOK(1)&T(NOW()),0))
    4. Click OK
    5. Come to the Cell, where you want to enter the Formula
    6. Enter the Formula as =countsheets
    7. Click Enter

    ReplyDelete
    Replies
    1. Your solution is great! It will automatically update the number after you added 1 or more Sheet to your workbook.

      Delete
  10. wroking.... :)

    - rupali

    ReplyDelete
  11. It does not work, because excel does not reconyze the function 'GET.WORKBOOK'. Anybody can help me? It seems that it is not among the available functions. Does it depend on the edition?

    ReplyDelete
  12. MANY THANKS !!!
    Brahim.

    ReplyDelete
  13. Thank you boss
    -excel student

    ReplyDelete
  14. Excel 2010, I have 10 sheets but only returning 1. Followed instructions correctly. Why?
    =COUNTA(INDEX(CountSheets,0))

    ReplyDelete
  15. Awesome. Pretty easy to do it. Thanks a lot.

    Also to Rashid who brought a modification of the formula and works great also.

    ReplyDelete
  16. Hi,
    i have the same problem on 2013
    Iv followed instructions and it returns 1 no matter how many sheets i have.

    ReplyDelete
  17. thnks, works like a charm

    ReplyDelete
  18. Thanks, Its fantastic indirect way to count sheets

    ReplyDelete
  19. I am using office 2007, i have followed the above steps, but i am getting couint as only 1. Can some one help us please.

    ReplyDelete
  20. Thanks mate.
    Henry Bruce Lawson

    ReplyDelete
  21. Perfect solution, thanks

    ReplyDelete
  22. This comment has been removed by the author.

    ReplyDelete
  23. Hello Saran,

    Could you modified this formula for me to know the current number of every single worksheet by sequence of the sheet

    e.g : Column1,Row1 (Every sheets)

    Sheet1=1
    Sheet2=2
    Sheet3=3

    And if I copied one of those 3 sheets in between, the formula will automatically counting it

    e.g:
    Sheet1=1
    Sheet1(1)=2 (newly copied sheet)
    Sheet2=3
    Sheet3=4

    Thanks in advance

    ReplyDelete
  24. Thank You So Much. God Bless You

    ReplyDelete
  25. For all those who got 1 as the result, they might have made the mistake of naming the "Macro" differently. If you name the macro as xyz then the formula should be =COUNTA(INDEX(xyz,0)) and not as =COUNTA(INDEX(CountSheets,0))

    ReplyDelete
  26. Great it works with excel 2010 but after adding more sheets it doesn't count automatically, so i have to double click the formula and press enter...it works

    ReplyDelete
  27. Is there a way to do this so that you are counting the sheets in a separate workbook?

    ReplyDelete
  28. Good Solution !
    But if some sheet have more than 2 pages it can't count.
    How can it count total pages in workbook?
    Thank You

    ReplyDelete
  29. Works great in Excel 2010
    Thanks for sharing the solution

    ReplyDelete
  30. How do I count sheets minus 2 sheets?

    Thank you!

    ReplyDelete
    Replies
    1. Just put a -2 at the end:

      =COUNTA(INDEX(CountSheets,0))-2

      Delete
  31. I don't know where to go to find out this question i have. Maybe one of you can tell me. I have a workbook with many sheets. If i want to enter something say in the 20th page then on the 5th page. I have to scan all the sheets to find them. Is there a way that you can "wrap" the sheets so all the sheets show and all i would have to do is click on it. it would be nice to be able to see all the sheets

    ReplyDelete
  32. very helpful thank you

    ReplyDelete
  33. Super helpful thanks!

    Anyone know how a formula to get worksheet number out of total number of worksheets? So, worksheet 5 of 10 for example. Thanks!

    ReplyDelete
  34. Awesome. It was very helpful. Thank you so much..

    ReplyDelete
  35. Overweight is a major problem of majority of people now days, due to overweight people have to face so embarrassment for going to any parties. Because of overweight you suffer many diseases like diabetes, heart problem. Blood pressure, etc. youRead More Best Slimming Tea’s Review Best Slimming Teas

    ReplyDelete
  36. Phen24 is a complete weight loss solution which works around the clock, unlike any other pills available in the market.

    ReplyDelete

Share your comment here..