Use SUMPRODUCT to Match Numbers to Text Headers in Excel
Question details
Match numeric values in a table column to headers with a text suffix inside a SUMPRODUCT calculation.

- 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.
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.
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.
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.
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.
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.

Use a Helper Column to Look Up Each Matching Header
Use this method if your Excel version does not expand the row and column arrays in the direct formula.
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. 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. 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. 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. 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.

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.




