Function Problems

How to Use SUMPRODUCT with Repeated Column Headers in Excel

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs a dynamic formula in Excel to identify repeated column headers (such as cost codes), sum the matching values, and multiply the row results by a corresponding completion percentage.

Product: Excel
Device & OS: not provided
Scenario: Calculating weighted totals across a dataset with multiple identical column headers without manually selecting individual columns.
Observed behavior: The user is looking for a way to automate filtering and calculating based on repeated header criteria to replace a manual, error-prone column selection process.

Before you start

Ensure your version of Excel or WPS Office supports modern dynamic array functions, as this solution relies on the FILTER and XMATCH functions to process the data.

Solution 1Recommended

Use a Dynamic Array Formula with FILTER and XMATCH

Combining FILTER and XMATCH provides a highly efficient and dynamic way to extract data from identical column headers and multiply it by a percentage column.

While traditional SUMPRODUCT formulas can be used for this task, utilizing dynamic arrays simplifies the logic. By wrapping XMATCH inside ISNUMBER, you can locate the exact headers you need, filter the corresponding data, and perform row-by-row calculations.

Set up your criteria cell

Select a cell (for example, H18) and input the specific header name or cost code you want to filter and summarize.

Identify matching headers using XMATCH

Use the XMATCH function wrapped in ISNUMBER to find which columns in your header range match your criteria. The syntax looks like this: ISNUMBER(XMATCH($B$1:$M$1,H18)).

Filter the data range

Apply the FILTER function to your main data area (e.g., B2:M13) using the condition established in the previous step. This isolates only the columns containing the desired header.

Apply row-by-row multiplication

Multiply the filtered dynamic array by your percentage column (e.g., O2:O13) to calculate the weighted value for each row.

Sum the final results

Wrap the entire formula in a SUM function to aggregate the totals. The complete formula should look like this: =SUM((FILTER($B$2:$M$13,ISNUMBER(XMATCH($B$1:$M$1,H18))))*($O$2:$O$13)).

Dynamic Updates: As long as you use absolute references for your ranges, you can safely copy this formula down or across your summary table. Any new data added within the referenced boundaries will be calculated automatically.

Advanced Spreadsheet Capabilities

Calculate Complex Array Formulas Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, XMATCH, and SUMPRODUCT, allowing you to solve complex data scenarios efficiently while maintaining 100% compatibility with Microsoft Excel formats.

1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx workbook containing the repeated headers.
2. Input the array formula: Select the target cell and type the combined SUM, FILTER, and XMATCH formula, just as you would in standard Excel.
3. Calculate and evaluate: Press Enter to instantly calculate the dynamic array formula and review your dynamically summarized totals.

Full support for modern dynamic array functionsSeamlessly opens and edits Microsoft Excel (.xlsx) files without formatting lossLightweight application that runs smoothly on all major operating systemsIntuitive interface for managing large datasets and repeated column structures

microsoft office alternative - wps office

Frequently Asked Questions

Can I use standard SUMPRODUCT instead of FILTER for repeated headers?

Yes, standard SUMPRODUCT can be used by multiplying the data array by a boolean array of header matches, such as =SUMPRODUCT((B1:M1=H18)*(B2:M13)*O2:O13). However, the FILTER and XMATCH method is often preferred in modern spreadsheet applications for better performance on large datasets.

Why is my dynamic array formula returning a #CALC! or #VALUE! error?

A #CALC! error typically occurs if the FILTER function finds no matching headers in your specified range. A #VALUE! error usually happens if the ranges being multiplied (e.g., the filtered columns and the percentage column) do not share the exact same number of rows.

Does WPS Spreadsheet support the FILTER and XMATCH functions?

Yes, the latest versions of WPS Office fully support modern dynamic array functions, including FILTER, XMATCH, UNIQUE, and SORT, ensuring complete compatibility with newer Excel array formulas.

Excel & Data Problems