SHEETS

In this comprehensive guide, we will explore the Excel formula SHEETS, which is used to count the number of sheets in a workbook or a specified reference. This function is particularly useful when working with large workbooks containing multiple sheets, as it allows you to quickly determine the total number of sheets without manually counting them. We will cover the syntax of the SHEETS formula, provide examples of its usage, share tips and tricks, discuss common mistakes, troubleshoot issues, and introduce related formulae.

SHEETS Syntax

The syntax for the SHEETS function is quite simple, as it only requires one optional argument:

SHEETS([reference])

The [reference] argument is optional and can be a cell, range, or a named range. If the reference is provided, the function will return the number of sheets that the reference spans across. If the reference is omitted, the function will return the total number of sheets in the workbook.

SHEETS Examples

Let’s explore some examples of how the SHEETS function can be used in different scenarios:

Example 1: Counting the total number of sheets in a workbook

To count the total number of sheets in a workbook, simply use the SHEETS function without any arguments:

=SHEETS()

This formula will return the total number of sheets in the workbook, regardless of their content or visibility (hidden or visible).

Example 2: Counting the number of sheets in a specified range

If you want to count the number of sheets that a specific range spans across, provide the range as the reference argument:

=SHEETS(Sheet1!A1:Sheet3!A1)

This formula will return the number of sheets between Sheet1 and Sheet3, inclusive. In this case, the result would be 3.

SHEETS Tips & Tricks

Here are some tips and tricks to help you make the most of the SHEETS function:

Tip 1: Counting visible sheets only

If you want to count only the visible sheets in a workbook, you can use a combination of the SHEETS and ISVISIBLE functions in an array formula:

=SUM(N(ISVISIBLE(INDIRECT(“Sheet”&ROW(1:100)&”!A1″))))

This formula assumes that there are no more than 100 sheets in the workbook. Adjust the number accordingly if you have more sheets. Remember to enter this formula as an array formula by pressing Ctrl+Shift+Enter.

Tip 2: Counting sheets with specific content

If you want to count the number of sheets containing specific content, you can use a combination of the SHEETS, INDIRECT, and COUNTIF functions in an array formula:

=SUM(N(COUNTIF(INDIRECT(“Sheet”&ROW(1:100)&”!A1:A10″),”Specific Content”)>0))

This formula assumes that there are no more than 100 sheets in the workbook and that you are looking for the specific content in the range A1:A10 on each sheet. Adjust the numbers and range accordingly. Enter this formula as an array formula by pressing Ctrl+Shift+Enter.

Common Mistakes When Using SHEETS

Here are some common mistakes to avoid when using the SHEETS function:

Mistake 1: Providing an invalid reference

Ensure that the reference provided is valid and exists in the workbook. If the reference is invalid, the SHEETS function will return a #REF! error.

Mistake 2: Forgetting to enter an array formula

When using the SHEETS function in combination with other functions to count visible sheets or sheets with specific content, remember to enter the formula as an array formula by pressing Ctrl+Shift+Enter.

Why Isn’t My SHEETS Function Working?

If your SHEETS function is not working as expected, consider the following troubleshooting steps:

Step 1: Check for errors

Ensure that there are no errors in the formula, such as a #REF! error caused by an invalid reference. Correct any errors and try again.

Step 2: Verify the reference

Make sure that the reference provided is valid and exists in the workbook. If the reference is incorrect, update it accordingly.

Step 3: Ensure proper usage of array formulas

If you are using the SHEETS function in an array formula, make sure to enter the formula correctly by pressing Ctrl+Shift+Enter.

SHEETS: Related Formulae

Here are some related formulae that you might find useful when working with the SHEETS function:

1. SHEET

The SHEET function returns the sheet number of a specified reference or the active sheet if no reference is provided.

2. COUNTIF

The COUNTIF function counts the number of cells within a range that meet a specified criterion.

3. INDIRECT

The INDIRECT function returns the value of a cell specified by a text string, allowing you to create dynamic cell references.

4. ROW

The ROW function returns the row number of a specified cell or the first cell in a range.

5. ISVISIBLE (VBA)

Although not a built-in Excel function, the ISVISIBLE function can be created using VBA to determine if a sheet is visible or hidden.

Related

Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications
 
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.