Formula Errors

How to Use SUMPRODUCT to Match Data Across Rows and Columns in Excel

Ayan Masood Oct 10, 2026 868 views

Question details

The user needs to extract forecast sessions for a selected week, channel, and metric from Excel tables where criteria are arranged across both rows and columns.

How to Use SUMPRODUCT to Match Forecast Data by Week and Channel

Product: Excel
Device & OS: not provided
Scenario: Matching data based on multiple criteria arranged both horizontally and vertically, such as week and metric headers on top, and channel headers on the side.
Observed behavior: Standard lookup formulas return a #VALUE! error because they struggle to process two-dimensional criteria arrays simultaneously.

Before you start

Verify that your criteria ranges and the final value range have exactly matching dimensions to prevent the #VALUE! error when using the SUMPRODUCT function.

Solution 1Recommended

Use SUMPRODUCT for Two-Dimensional Array Matching

The SUMPRODUCT function evaluates multiple arrays simultaneously, making it ideal for matching criteria spread across both rows and columns where standard lookups fail.

When dealing with 2D datasets, SUMPRODUCT can multiply boolean arrays (True/False values converted to 1/0) against the main value array to extract the exact intersection.

Identify your criteria ranges

Locate the cell references for your row criteria (e.g., Channel data in C3:C6) and column criteria (e.g., Week data in D1:G1, Metric data in D2:G2).

Construct the SUMPRODUCT formula

Select the target cell and use the syntax =SUMPRODUCT((RowRange=RowCriteria)*(ColumnRange1=ColumnCriteria1)*(ColumnRange2=ColumnCriteria2)*ValueRange).

Apply absolute and relative referencing

Press F4 to lock your main data ranges (e.g., $C$3:$C$6). Use relative or mixed references for the criteria cells (like $I2 or K$1) so the formula fills correctly across your summary table.

Enter the final formula

Type =SUMPRODUCT(($C$3:$C$6=$I2)*($D$2:$G$2=K$1)*($D$1:$G$1=$J2)*$D$3:$G$6) and press Enter to calculate the matching forecast data.

Use SUMPRODUCT for Two-Dimensional Array Matching

Reference Check: Ensure the placement of absolute ($) and relative references is strictly accurate, otherwise dragging the formula to adjacent cells will shift the calculation ranges.

Solution 2

Restructure Data for a PivotTable

If managing complex array formulas becomes difficult, flattening the data allows for easy PivotTable analysis without advanced formulas.

Resolve Formula Errors with WPS Spreadsheet

Use SUMPRODUCT Seamlessly in WPS Office

WPS Office offers robust support for advanced array functions like SUMPRODUCT, helping you process complex multi-dimensional lookups with ease and precision.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your forecast and Google Analytics data.
2. Select the target cell: Click the cell where you want the matched forecast data to appear.
3. Enter the formula: Type =SUMPRODUCT( and use your mouse to highlight the row and column criteria arrays required for your conditions.
4. Apply cell locks: Press F4 on your keyboard while selecting ranges to quickly apply absolute references to your data matrix.
5. Calculate the result: Close the parentheses and press Enter to instantly calculate your matched result without needing Ctrl+Shift+Enter.

100% compatible with Microsoft Excel formulas and array functionsLightweight application that processes large forecast datasets quicklyIntuitive formula builder with built-in error checking for array dimensionsFree to use for personal and professional data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMPRODUCT formula return a #VALUE! error?

A #VALUE! error typically occurs if the array ranges within your SUMPRODUCT formula do not share the exact same dimensions. Ensure that your row criteria range length matches the value range height, and the column criteria range width matches the value range width.

Can I use INDEX and MATCH instead of SUMPRODUCT for this dataset?

Yes, but extracting data based on both row and column criteria arrays often requires complex nested MATCH functions. SUMPRODUCT is generally much cleaner for two-dimensional mathematical lookups.

Do I need to press Ctrl+Shift+Enter when using SUMPRODUCT?

No, SUMPRODUCT is designed to handle arrays natively in the spreadsheet. You only need to press Enter after typing the formula, even when multiplying multiple criteria arrays together.

Excel & Data Problems