Use COUNTA and INDIRECT to Count Cells in Another Excel Worksheet
Question details
The user needs a dynamic, copyable formula to count nonblank cells in a specific named range located on a different worksheet, using the worksheet name listed in a reference cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a summary sheet where column A lists different worksheet names, and column B requires a formula to automatically count the non-empty cells in a specific range for each corresponding sheet.
- Observed behavior
- The user wants to accomplish the cell count dynamically without manually updating the worksheet reference string inside the formula for every single row.
Verify that the worksheet names listed in your reference column match the actual tab names exactly, including any spaces, to avoid #REF! errors when the formula executes.
Combine COUNTA and INDIRECT to Create a Dynamic Formula
Use the INDIRECT function to construct a valid cell reference from text strings, and wrap it inside the COUNTA function to count the nonblank cells on the target worksheet.
The INDIRECT function is designed to convert text strings into valid Excel references. By concatenating the worksheet name from one cell and the target range from another, you can build a dynamic path that updates automatically when copied down a column.
Ensure that your target range name or reference (e.g., A1:A50) is entered in cell A1, and the name of the target worksheet is entered in cell A2.
Click on cell B2 (or your desired output cell) and input the following formula: =COUNTA(INDIRECT("'"&A2&"'!"&$A$1))
Press Enter to execute the formula. Then, click the bottom-right corner of cell B2 and drag the fill handle down to copy the formula for any other worksheet names listed in column A.
Dynamically Count Cells Across Sheets with WPS Spreadsheet
You can easily construct dynamic cell references and perform complex cross-sheet calculations using COUNTA and INDIRECT functions in WPS Spreadsheet, a lightweight and fully compatible data analysis tool.
- 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your summary table and target sheets.
- 2. Prepare Your Reference Cells: List your target worksheet names in column A (starting at A2) and place the range you want to count in cell A1.
- 3. Input the Dynamic Formula: In cell B2, type the formula: =COUNTA(INDIRECT("'"&A2&"'!"&$A$1)) and press Enter.
- 4. Drag to Autofill: Use the fill handle on the bottom-right of the active cell to drag the formula down the column, applying the dynamic count to all sheets.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error?
A #REF! error occurs if the worksheet name referenced in your cell contains typos, the sheet has been deleted, or the specified range is invalid. Check that the text in your cell perfectly matches the actual sheet tab name.
Does the INDIRECT function work if referencing a closed workbook?
No, the INDIRECT function requires any referenced external workbook to remain open. If the external file is closed, the formula will return a #REF! error. However, this does not apply to referencing different sheets within the same active workbook.
Why are single quotes used around the sheet name in the formula?
Single quotes are required when building dynamic cross-sheet references to account for worksheet names that contain spaces or special characters. Without them, Excel will fail to recognize the sheet name correctly and will produce an error.
