Function Problems

How to Sum Selected Columns Matching Specific Criteria in Excel

Elise Williams Sep 28, 2026 869 views

Question details

The user needs to create a formula that evaluates specific column headers or criteria and sums the corresponding values across nonadjacent columns, potentially matching data from another worksheet.

How to Sum Selected Columns with Matching Criteria in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Summarizing data across multiple, nonadjacent columns based on dynamic headers or criteria like "salary" or "day".
Observed behavior: The user requires a robust array or conditional formula to evaluate column headers and sum the corresponding row values without manually adding individual cells together.

Before you start

Ensure your source data is organized in a clear tabular format with distinct column headers, and verify that the criteria you want to match are spelled exactly as they appear in the dataset to avoid formula calculation errors.

Solution 1Recommended

Use the SUMPRODUCT Function to Sum Matching Columns

SUMPRODUCT is the most effective way to evaluate an array of column headers and sum the values in the corresponding columns for specific rows.

The SUMPRODUCT function can multiply arrays together and sum the results. By comparing a header range to a specific text string, it generates an array of 1s (true) and 0s (false), effectively filtering which columns to include in the sum.

Select the destination cell

Click on the cell where you want the total sum to be displayed.

Enter the SUMPRODUCT formula structure

Type the formula using the syntax =SUMPRODUCT((Header_Range="Criterion")*Data_Range).

Customize ranges for your data

For example, to sum all columns named "salary" in row 19 between columns O and BD, use =SUMPRODUCT((O$4:BD$4="salary")*O19:BD19).

Calculate the result

Press Enter. The formula evaluates the headers and calculates the total for the columns matching your specific criteria.

Use the SUMPRODUCT Function to Sum Matching Columns

Cross-Sheet Compatibility: To match criteria on another worksheet, simply prepend the sheet name to the range, like =SUMPRODUCT((Sheet2!O$4:BD$4="salary")*O19:BD19).

Solution 2

Use SUMIF to Sum Values Across a Single Row Based on Headers

If you only need to evaluate a single row of headers and sum the corresponding values directly below them, SUMIF is a simpler alternative.

Powerful Spreadsheet Software

Use WPS Spreadsheet to Handle Complex Formulas

WPS Spreadsheet fully supports advanced array formulas like SUMPRODUCT and SUMIF, allowing you to easily sum nonadjacent columns and manage complex data across multiple worksheets with high performance.

1. Open your workbook: Launch WPS Office and open your .xlsx or .csv dataset in WPS Spreadsheet.
2. Select the target cell: Click on the cell where you want your dynamic, criteria-based sum to appear.
3. Enter the formula: Type your SUMPRODUCT or SUMIF formula exactly as you would in Microsoft Excel.
4. Get instant results: Press Enter to instantly calculate your cross-column totals based on matching headers.

Fully compatible with Microsoft Excel formulas and the .xlsx file format.Advanced formula syntax support for SUMPRODUCT, SUMIFS, and dynamic arrays.Lightweight, fast, and completely free to use for everyday data analysis tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use SUMPRODUCT with multiple criteria across different headers?

Yes, you can add more criteria by multiplying additional arrays. For example, =SUMPRODUCT((O$4:BD$4="salary")*(O$5:BD$5="Monday")*O19:BD19) evaluates two different header rows before summing the final data.

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

This error usually happens if the criteria range and the sum range are not exactly the same size. Ensure both ranges cover the same number of columns (for instance, O4:BD4 and O19:BD19 both span exactly 42 columns).

How do I dynamically reference a matching criterion from another sheet?

Instead of typing the text string inside the formula, click the cell on the other worksheet containing the criterion. Your formula will then look something like =SUMPRODUCT((O$4:BD$4=SummarySheet!A1)*O19:BD19).

Can SUMPRODUCT sum multiple rows at once based on column headers?

Yes, you can expand the data range. For example, =SUMPRODUCT((O$4:BD$4="salary")*O19:BD25) will sum all values in columns labeled "salary" across rows 19 through 25 simultaneously.

Excel & Data Problems