Count Cells Below a Named Header on Another Excel Sheet
Question details
Count numeric values greater than 4 below a named header on another worksheet, even if the column position changes.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains headers across a row, and the column for the target header may move.
- Observed behavior
- The desired result is a count of qualifying numbers in the column identified by its header.
Check the source sheet name, the exact header text, and the rows and columns that contain your data. The header range and data range must cover the same columns.
Use SUMPRODUCT to Count Values Under the Matching Header
Use this formula when the header stays in the same row but its column position may change.
On the worksheet where you want the count, click an empty cell.
Enter =SUMPRODUCT((Sheet2!$A$1:$I$1="Header 2")*ISNUMBER(Sheet2!$A$2:$I$23)*IFERROR(--(Sheet2!$A$2:$I$23>4),0)) and press Enter.
Replace Sheet2 and Header 2 with your sheet name and header. Expand A1:I1 and A2:I23 as needed, keeping their columns aligned.

Count Values by Header in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use SUMPRODUCT to count qualifying numbers beneath a named header on another sheet.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your Excel workbook.
- 2. Check the source range: Click the source sheet tab and identify the header row and data rows to include.
- 3. Enter the count formula: Click an empty cell on the destination sheet and enter =SUMPRODUCT((Sheet2!$A$1:$I$1="Header 2")*ISNUMBER(Sheet2!$A$2:$I$23)*IFERROR(--(Sheet2!$A$2:$I$23>4),0)).
- 4. Adapt and confirm: Change the sheet name, header, and ranges to match your workbook, then press Enter.

Frequently Asked Questions
Will the formula still work if the column moves?
Yes, as long as the header and its data remain within the specified header and data ranges. The formula matches the header rather than relying on a fixed column position.
How do I count values above a different number?
In the formula bar, replace >4 with your desired condition, such as >10, and press Enter.
What if my sheet name contains spaces?
Enclose the sheet name in single quotes in both references, such as 'Sales Data'!$A$1:$I$1 and 'Sales Data'!$A$2:$I$23.
Why does the formula return zero?
Check that the header text matches a cell in the specified header row and that the data range includes the rows you want to count. Zero is also correct if no numeric values exceed the threshold.




