Use SUMPRODUCT to Match Text and Date Headers in Excel
Question details
Sum a two-dimensional data range using a text criterion for row labels and a date or header criterion for columns.

- 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.
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.
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.
On Sheet2, locate the row labels in B4:B11, the column headers in C3:J3, and the numeric data in C4:J11.
On the worksheet where you want the result, enter the text to match in B5 and the date or header to match in C5.
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.
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 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. Open the workbook: Open WPS Spreadsheets and select Open to choose your workbook.
- 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. Enter the criteria: On the result worksheet, enter the text criterion in B5 and the date or header criterion in C5.
- 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.

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.




