Function Problems

Count Cells Below a Named Header on Another Excel Sheet

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 868 views

Question details

Count numeric values greater than 4 below a named header on another worksheet, even if the column position changes.

Count Cells Below a Named Header on Another Excel Sheet
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

On the worksheet where you want the count, click an empty cell.

2
Enter the formula

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.

3
Adjust the references

Replace Sheet2 and Header 2 with your sheet name and header. Expand A1:I1 and A2:I23 as needed, keeping their columns aligned.

Use SUMPRODUCT to Count Values Under the Matching Header
How the count works: The formula counts numeric values greater than 4 and ignores text and errors. If the header appears more than once, it counts values under every matching header; if no header matches, it returns 0.
Count by header in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your Excel workbook.
  2. 2. Check the source range: Click the source sheet tab and identify the header row and data rows to include.
  3. 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. 4. Adapt and confirm: Change the sheet name, header, and ranges to match your workbook, then press Enter.
Count values by header even when the column movesOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and worksheet tabsWork with spreadsheets using the free WPS Office suite
microsoft office alternative - wps office

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.