Function Problems

Use SUMPRODUCT to Match Text and Date Headers in Excel

Huda QurayshiHuda Qurayshi Nov 18, 2026 868 views

Question details

Sum a two-dimensional data range using a text criterion for row labels and a date or header criterion for columns.

Use SUMPRODUCT to Match Text and Date Headers in Excel
Product
Microsoft Excel and WPS Spreadsheets
Device & OS
not provided
Scenario
A worksheet contains text labels down one column, date or other headers across a row, and numeric values in the grid between them.
Observed behavior
The desired result is the total of values where both the row label and column header match the specified criteria.
Before you start

Check that the row-label range has the same number of rows as the data grid and that the header range has the same number of columns. If matching dates, make sure the criterion and headers contain matching date values rather than text or differing time components.

Solution 1Recommended

Sum matching rows and columns with SUMPRODUCT

Use this formula when text labels and date or other headers identify the values to total in a two-dimensional grid.

The formula compares each row label with the text criterion and each column header with the header criterion, then sums the matching cells in the data grid. If more than one row or column matches, all matching intersections are included.

1
Identify the source ranges

On Sheet2, locate the row labels in B4:B11, the column headers in C3:J3, and the numeric data in C4:J11.

2
Enter the criteria

On the worksheet where you want the result, enter the text to match in B5 and the date or header to match in C5.

3
Enter the formula

Select an empty result cell and enter =SUMPRODUCT((Sheet2!$B$4:$B$11=B5)*(Sheet2!$C$3:$J$3=C5)*Sheet2!$C$4:$J$11), then press Enter.

4
Adjust the references

If your data is elsewhere, edit the Sheet2 ranges and criterion cells in the formula to match your workbook, keeping the row and column dimensions aligned.

Sum matching rows and columns with SUMPRODUCT
Check exact matches: A date with a time component is not equal to the same date at midnight. Also check for extra spaces in text labels if an expected match returns zero.
WPS Spreadsheets

Sum matching text and date headers in WPS Spreadsheets

Use SUMPRODUCT in WPS Spreadsheets to match row labels and column headers and total their intersecting values.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your workbook.
  2. 2. Check the source grid: Open Sheet2 and check that B4:B11 contains row labels, C3:J3 contains headers, and C4:J11 contains numeric values, or identify your equivalent ranges.
  3. 3. Enter the criteria: On the result worksheet, enter the text criterion in B5 and the date or header criterion in C5.
  4. 4. Calculate the total: Select an empty cell, enter =SUMPRODUCT((Sheet2!$B$4:$B$11=B5)*(Sheet2!$C$3:$J$3=C5)*Sheet2!$C$4:$J$11), and press Enter. Adjust the references if your layout differs.
Sum a grid using both text and date or header criteria.Open and edit Microsoft Excel XLSX and XLS files.Use familiar worksheet formulas and cell references.
microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMPRODUCT return zero when the date looks correct?

The header and criterion must contain equal date values. Check whether one is stored as text or includes a time component, and also check the text label for extra spaces.

What happens if several rows or headers match?

SUMPRODUCT adds every cell at the intersections of all matching rows and columns. For a single intersection, use criteria that identify one row and one column.

Why am I getting a value error?

Check the ranges in the formula: the row-label range must match the data grid's height, and the header range must match its width. Also check that the data grid contains numeric values suitable for multiplication.

Can I match a text header instead of a date?

Yes. Enter the exact header text in C5 and use the same formula; the comparison against Sheet2!C3:J3 works with text headers as well as dates.