How do I count the total number of sheets in my sales workbook?

Use =SHEETS() with no arguments. It returns the total count of all sheets, regardless of which sheet the formula is on. This works for any multi-sheet structure and is essential for validation workflows.

Can SHEETS tell me a sheet's name instead of just its number?

No, SHEETS returns only the numeric sheet position, not the name. To retrieve sheet names programmatically, maintain a separate list or use advanced techniques like FORMULATEXT to extract names from cell references.

What happens if I reference a sheet that was renamed or deleted?

You'll get a #REF! error because the reference no longer points to a valid sheet. Always verify sheet names are spelled correctly and sheets haven't been removed before using them in SHEETS formulas.

Why would I use SHEETS with a reference in a formula instead of just manually checking the sheet tabs?

Using SHEETS in formulas automates validation in dynamic reports—you can verify data location, prevent errors from missing sheets, and ensure consolidation only proceeds when all required regional sales sheets are present and in the expected positions.

SHEETS function

SHEETS returns the total number of sheets in the workbook, or the sheet number containing a specified cell reference.

=SHEETS([reference])

Generate a SHEETS formula

Describe what you need. The generator will reach for SHEETS where SHEETS is the right tool, and tell you when it is not.

Arguments

How SHEETS reads its arguments

Argument	Required	Description
reference	Optional	Optional. A cell or range reference (e.g., 'East'!A1:B10). If omitted, returns the total number of sheets in the workbook; if provided, returns the numeric sheet position containing that reference.

Returns

A positive integer representing either the total sheet count or the sheet position of a given reference.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Count total sheets in a multi-region sales workbook

Order ID	Region	Rep	Units	Unit Price	Order Date
ORD001	West	Alice	100	$25	2024-01-15
ORD002	East	Bob	150	$30	2024-01-20
ORD003	North	Charlie	200	$20	2024-02-10

=SHEETS()

Result: 3

The workbook contains three separate regional sheets (West, East, North), each with a sales log. SHEETS() returns 3, confirming all expected sheets exist before running consolidation. This is essential when auditing a distributed sales workbook—a missing region would return 2, alerting you to check for incomplete setup.

2. Identify which sheet contains a sales rep's primary records

Order ID	Region	Rep	Units	Unit Price	Order Date
ORD002	East	Bob	150	$30	2024-01-20
ORD004	South	Diana	75	$35	2024-02-15
ORD005	Central	Eve	120	$28	2024-03-01

=SHEETS('East'!B2:B8)

Result: 2

The 'East' sheet is positioned as the second sheet in the workbook, so SHEETS returns 2. This is useful in formulas that need to programmatically identify sheet position—for example, when building a dynamic reference that must validate the correct sheet before pulling a rep's order totals.

3. Validate archive location before consolidating historical orders

Order ID	Region	Rep	Units	Unit Price	Order Date
ORD001	West	Alice	100	$25	2024-01-15
ORD002	East	Bob	150	$30	2024-01-20
ORD003	North	Charlie	200	$20	2024-02-10
ORD004	South	Diana	75	$35	2024-02-15

=IF(SHEETS('Archive'!A1:F100)=4,"Ready to Consolidate","Check Archive Sheet")

Result: Ready to Consolidate

If archived orders are stored on sheet 4 (as expected), SHEETS returns 4, the IF condition evaluates true, and consolidation proceeds. If the Archive sheet is missing or on a different position, the condition fails and flags a warning. This prevents data errors in multi-sheet consolidation workflows.

Common errors

Which SHEETS error are you seeing?

Error	Why it happens	How to fix it
#NAME?	Function name is misspelled (e.g., =SHEET() with only one S, or =SHETS()) or the spreadsheet doesn't recognize SHEETS as a valid function.	Verify the function spelling is exactly SHEETS with an S at the end. Check your spreadsheet application version supports SHEETS (Google Sheets and modern Excel do; older versions may not).
#REF!	The reference argument points to a sheet that no longer exists (e.g., =SHEETS('DeletedRegion'!A1) after that sheet was removed or renamed).	Verify the sheet name exists and is spelled correctly. Update the reference to a valid sheet name, or use SHEETS() without arguments to get the total sheet count instead.
#VALUE!	The reference argument is not a valid cell or range (e.g., =SHEETS("text string"), =SHEETS(123), or =SHEETS(A1:B10) without a sheet name prefix).	Ensure the reference includes a sheet name and range, like 'Sales'!A1:B10 or 'East'!C5. Remove quotes around references; quotes are only for literal text, not cell ranges.

Tips and when to use something else

Use SHEETS() without arguments for a quick count of all sheets—critical for auditing multi-region sales workbooks before consolidation or reporting.
Combine SHEETS with IF or IFERROR to validate that required data sheets exist at expected positions before running dependent VLOOKUP or INDEX formulas.
SHEETS returns only position numbers, not names; if you need dynamic sheet selection by condition, use INDIRECT combined with SHEET() or construct a lookup table of sheet names.
In audit workflows, compare SHEETS() against a known sheet count to detect missing or accidentally deleted regions before proceeding with cross-sheet consolidation of sales orders.

Related functions

SHEETSHEET returns the index number of a sheet containing a reference, or the current sheet's number if no argument is given.INDIRECTINDIRECT returns the value or reference specified by a text string, letting you build dynamic cell or range references on the fly.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.

Frequently asked questions

How do I count the total number of sheets in my sales workbook?: Use =SHEETS() with no arguments. It returns the total count of all sheets, regardless of which sheet the formula is on. This works for any multi-sheet structure and is essential for validation workflows.
Can SHEETS tell me a sheet's name instead of just its number?: No, SHEETS returns only the numeric sheet position, not the name. To retrieve sheet names programmatically, maintain a separate list or use advanced techniques like FORMULATEXT to extract names from cell references.
What happens if I reference a sheet that was renamed or deleted?: You'll get a #REF! error because the reference no longer points to a valid sheet. Always verify sheet names are spelled correctly and sheets haven't been removed before using them in SHEETS formulas.
Why would I use SHEETS with a reference in a formula instead of just manually checking the sheet tabs?: Using SHEETS in formulas automates validation in dynamic reports—you can verify data location, prevent errors from missing sheets, and ensure consolidation only proceeds when all required regional sales sheets are present and in the expected positions.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17