How to Count the Number of Sheets in an Excel Workbook Range
Question details
The user needs to find a way to count the total number of worksheets in an Excel workbook or the number of sheets included within a specific workbook range.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a complex workbook and needing to calculate the exact number of worksheets present, or determining how many sheets are part of a specific 3D reference range.
- Observed behavior
- The user is looking for the correct formula or approach to yield the count of sheets within the targeted workbook scope.
Before applying a formula, clarify whether you need to count every single worksheet in the entire file or only the sheets that fall between two specific tabs (a 3D range reference).
Use the Built-in SHEETS Function
The easiest way to count worksheets is by using the SHEETS function, which can count total workbook sheets or sheets within a specified 3D range.
Introduced in newer versions of Excel, the SHEETS function returns the number of sheets in a reference. If you omit the reference, it simply returns the total number of sheets in the current workbook.
Click on the blank cell where you want the sheet count result to be displayed.
Type =SHEETS() into the formula bar and press Enter. This will immediately display the total count of all sheets, including hidden ones.
To count only the sheets between specific tabs, type a 3D reference like =SHEETS(Sheet1:Sheet3!A1) and press Enter.
Use a VBA Macro to Count Sheets
If you are using an older spreadsheet version or need to incorporate the sheet count into a larger automated process, you can use a simple VBA macro.
Count Sheets Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced formulas like the SHEETS function and 3D references, making it incredibly simple to manage complex workbooks and extract data insights.
- 1. Open your file in WPS Office: Launch WPS Spreadsheet and open the workbook containing the sheets you want to count.
- 2. Enter the formula: Click on any empty cell and type =SHEETS().
- 3. Get the result instantly: Press Enter, and WPS Spreadsheet will instantly calculate and display the total number of sheets.
Frequently Asked Questions
Does the SHEETS function count hidden worksheets?
Yes, the =SHEETS() function counts all worksheets in the referenced workbook or range, including those that are marked as hidden or very hidden.
How do I count values across a range of sheets instead of counting the sheets themselves?
To count numeric values across multiple sheets, you can use a 3D reference with the COUNT function. For example, typing =COUNT(Sheet1:Sheet3!A1:A10) will count all cells containing numbers in the range A1:A10 across Sheet1, Sheet2, and Sheet3.
Can I count only worksheets with specific text in their names?
Standard Excel formulas do not natively read and filter sheet names. To count sheets based on a specific naming convention, you will need to write a custom VBA script that loops through the sheets and counts those matching your criteria.
