Sum matching columns

To sum values in columns by matching column headers, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in J5 is: =SUMPRODUCT(data*(LEFT(headers)=J4)) where data is the named range B5:G14, and headers is the named range B4:G4. The formula sums columns where headers begin with "A" and returns 201. Note: In the current version of Excel you can also use the FILTER function, as explained below.

April 14, 2025 · 1 min · 82 words · Susan Pham

Table of Contents

Inside SUMPRODUCT, this array is multiplied by “data”.

The FILTER function uses this array to select columns in the named rangedata.

Because thisarray is horizontal, FILTERautomatically filters on columns.

Excel formula: Sum matching columns and rows

For example, =LEFT(“apple”,3) returns “app”.

The output from FILTER is dynamic.

If source data or criteria change, FILTER will return a new set of results.

Excel formula: Sum every nth column

These values can be numbers, cell references, ranges, arrays, and constants, in any combination.

SUM can handle up to 255 individual arguments.

FILTER function basic example

Excel formula: Get relative column numbers in range

Excel formula: Sum columns based on adjacent criteria

Excel formula: Basic filter example

Excel SUMPRODUCT function

Excel LEFT function

Excel FILTER function

Excel SUM function