Function Problems

Use SUMPRODUCT to Match Numbers to Text Headers in Excel

Bushra ParveenBushra Parveen Nov 18, 2026 869 views

Question details

Match numeric values in a table column to headers with a text suffix inside a SUMPRODUCT calculation.

Use SUMPRODUCT to Match Numbers to Text Headers in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A table contains numeric values in Data[Top] and corresponding headers such as 1BR through 10BR. The user wants to count matching cells containing Top L while applying other row criteria.
Observed behavior
The numeric values must be converted to the same text format as the headers before they can be compared.
Before you start

Check that the table headers use the same suffix, such as BR, and that the header and data ranges cover the same columns. Confirm that MatchDashboard and Player1Dashboard are valid named references or replace them with cell references.

Solution 1Recommended

Append the Header Suffix in the SUMPRODUCT Formula

Use this approach when your Excel version supports expanding a column of values against a row of headers.

Concatenating Data[Top] with "BR" converts a value such as 1 into the text 1BR, which can match the corresponding header. The example divides the result by 12, as in the original calculation.

1
Enter the formula

Select the result cell and enter =SUMPRODUCT((Data[Match ID]=MatchDashboard)*(Data[Current Player]=Player1Dashboard)*(Data[Top]>0)*(Data[[#Headers],[1BR]:[10BR]]=Data[Top]&"BR")*(Data[[1BR]:[10BR]]="Top L"))/12.

2
Adjust the references

In the formula bar, replace Data, the named criteria, and the 1BR:10BR endpoints as needed to match your workbook. Use the same first and last columns in both structured ranges.

3
Check the result

Press Enter and compare the result with a few rows where Data[Top] identifies a header containing Top L. If the arrays do not expand as expected, use the helper-column method instead.

Append the Header Suffix in the SUMPRODUCT Formula
Work with spreadsheet formulas in WPS

Match Numeric Values to Headers in WPS Spreadsheet

Use a helper column with INDEX and MATCH in WPS Spreadsheet, then apply SUMPRODUCT to count the matching values and row criteria.

  1. 1. Open the workbook: Open WPS Spreadsheet, select Open, and choose the Excel workbook. For the following example, use columns A, B, and C for Match ID, Current Player, and Top, with headers 1BR through 10BR in E1:N1.
  2. 2. Enter the criteria: Enter the desired Match ID in P1 and the desired player in P2, or change those references in the final formula.
  3. 3. Add a lookup column: In O2, enter =IFERROR(INDEX($E2:$N2,1,MATCH(C2&"BR",$E$1:$N$1,0)),"") and fill it down for each data row.
  4. 4. Apply SUMPRODUCT: In a result cell, enter =SUMPRODUCT(($A$2:$A$100=$P$1)*($B$2:$B$100=$P$2)*($C$2:$C$100>0)*($O$2:$O$100="Top L"))/12. Change the ranges to match your data.
Match a number such as 1 to a header such as 1BRCombine header matches with multiple SUMPRODUCT criteriaOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does a number not match a header like 1BR?

The header is text, while the criterion is numeric. Append the suffix with &"BR" so that a value of 1 becomes the text 1BR before comparison.

What if my last header is named 0BR instead of 10BR?

Use your actual column name as the endpoint in both structured references, for example Data[[#Headers],[1BR]:[0BR]] and Data[[1BR]:[0BR]].

Why does the direct SUMPRODUCT formula return an error?

The Excel version may not expand the vertical Top values against the horizontal header row as needed. Check that the ranges align, or use INDEX and MATCH in a helper column before applying SUMPRODUCT.

Can I change or remove the division by 12?

Yes. The /12 at the end follows the original calculation. Change the divisor or remove it if you need a different normalization or the unadjusted count.