Function Problems

How to Sum Rows in Excel Based on a Value in Another Column

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to calculate the total across multiple columns in a row whenever a cell in a specific column (Column B) matches a certain type, such as 'p', 'pr', or 'pn'.

Product: Excel
Device & OS: not provided
Scenario: Aggregating multi-column numerical data dynamically based on categorical criteria stored in a single reference column.
Observed behavior: Requires a formula capable of processing multiple columns based on a single column's criteria, avoiding the need to manually sum or write lengthy SUMIF functions for each column individually.

Before you start

Ensure that your data range does not contain error values, as they will cause array formulas to return an error. Also, verify that your criteria column and your data columns have the exact same number of rows.

Solution 1Recommended

Use the SUMPRODUCT Function

The SUMPRODUCT function evaluates an array condition (checking the type in Column B) and multiplies it by the numerical values in the corresponding columns, providing a grand total across rows.

While the SUMIF function is great for single-column summing, it cannot sum across multiple columns at once. SUMPRODUCT is the most efficient array function for this multi-column criteria scenario.

Set up your criteria cell

Type the specific value you want to sum for (e.g., 'p') into an empty cell, for example, cell A20.

Enter the SUMPRODUCT formula

Select the cell where you want the final total to appear (like C21) and enter the formula: =SUMPRODUCT(($B$2:$B$13=A20)*($C$2:$I$13))

Adjust the ranges to match your data

Ensure that $B$2:$B$13 targets your exact criteria column, and $C$2:$I$13 covers all the columns containing the part values you want to total.

Copy the formula for other types

If you have other types like 'pr' and 'pn' listed below A20, click the bottom-right corner of the formula cell and drag it down to calculate their totals automatically.

Using Absolute References: Using dollar signs ($) locks the ranges in the formula. This ensures that when you copy the formula down for other types, the array range does not shift out of place.

Advanced Spreadsheets Made Easy

Calculate Complex Array Formulas Easily with WPS Spreadsheet

WPS Office fully supports advanced array functions like SUMPRODUCT, allowing you to sum rows based on dynamic criteria seamlessly. Enjoy an intuitive interface that makes managing complex data straightforward.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your dataset.
2. Apply the formula: Click the cell for your result and type the SUMPRODUCT formula just as you would in Excel.
3. Use AutoFill: Drag the fill handle to apply the calculation for all your specified types instantly.

Fully compatible with Microsoft Excel formulas and .xlsx files.Handles heavy array formulas like SUMPRODUCT effortlessly.Free, lightweight, and easy to use across Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

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

This typically occurs if the criteria range and the sum range have a different number of rows. Ensure that both ranges (e.g., $B$2:$B$13 and $C$2:$I$13) span the exact same row numbers.

Can I use SUMIFS instead of SUMPRODUCT to sum multiple columns?

SUMIFS is designed to sum a single column based on criteria. To sum multiple columns with SUMIFS, you would need to add multiple SUMIFS formulas together (e.g., SUMIFS(C:C...)+SUMIFS(D:D...)). SUMPRODUCT is much cleaner for multi-column ranges.

How do I sum for multiple criteria in Column B in a single cell?

If you want to sum if Column B is 'p' OR 'pn' simultaneously, you can use array constants within the SUMPRODUCT formula, such as: =SUMPRODUCT((ISNUMBER(MATCH($B$2:$B$13,{"p","pn"},0)))*($C$2:$I$13)).

Excel & Data Problems