Function Problems

Sum Rows in Excel Columns with Matching Text Headers

Camila MilosovichCamila Milosovich Nov 18, 2026 868 views

Question details

Sum values from columns whose headers contain specified text, using only rows within a selected date range.

Sum Rows in Excel Columns with Matching Text Headers
Product
Microsoft Excel for Microsoft 365
Device & OS
not provided
Scenario
A worksheet has horizontal column headers, dates in a separate column, and a block of values to sum.
Observed behavior
SUMIF cannot directly combine a horizontal header range with a differently sized vertical row range to select the desired block.
Before you start

Check that the header range matches the width of the data block and the date range matches its height. Make sure the date cells contain real dates and set the lower and upper date boundaries in L6 and L7.

Solution 1Recommended

Use FILTER and SEARCH to sum matching columns and dates

Use this Microsoft 365 formula when the rows to sum are determined by date boundaries.

The formula first filters rows by date, then filters columns whose headers contain the specified text. SEARCH is case-insensitive and matches text anywhere in a header.

1
Arrange the ranges

Place headers in E1:I1, dates in D2:D12, and the numeric values to sum in E2:I12. Enter the lower date in L6 and the upper date in L7.

2
Enter the formula

Select an empty cell and enter: =LET(data,$E$2:$I$12,dates,$D$2:$D$12,headers,$E$1:$I$1,period,FILTER(data,(dates>$L$6)*(dates<$L$7)),matched,FILTER(period,ISNUMBER(SEARCH("Blue",headers))),SUM(matched))

3
Adjust the criteria

Replace "Blue" with the text to find in the headers, and change the range references if your worksheet uses different cells. Press Enter to calculate the total.

Use FILTER and SEARCH to sum matching columns and dates
Date boundaries and empty matches: The formula excludes dates equal to L6 or L7. Change > and < to >= and <= to include the boundary values. FILTER returns a #CALC! error if no rows or headers match.
WPS Spreadsheet

Sum matching columns by date in WPS Spreadsheet

Use SUMPRODUCT and SEARCH in WPS Spreadsheet to sum numeric values where the column headers contain your text and the row dates fall between your chosen boundaries.

  1. 1. Open your workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Check the ranges: Confirm that headers are in E1:I1, dates are in D2:D12, numeric data is in E2:I12, and the date boundaries are in L6 and L7.
  3. 3. Enter the formula: Click an empty cell and enter =SUMPRODUCT($E$2:$I$12*--ISNUMBER(SEARCH("Blue",$E$1:$I$1))*--($D$2:$D$12>$L$6)*--($D$2:$D$12<$L$7)).
  4. 4. Adjust and calculate: Replace "Blue" and the cell references as needed, then press Enter. The formula excludes the two boundary dates; use >= and <= to include them.
Sum matching header columns and date-filtered rows in one formulaOpen and save Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

How do I match an entire header instead of part of it?

In the FILTER formula, replace ISNUMBER(SEARCH("Blue",headers)) with headers="Blue". This checks for the whole header text rather than a substring.

Why does the FILTER formula return #CALC!?

The date conditions may select no rows, or SEARCH may find no matching headers. Check the dates in L6 and L7, the header text, and the referenced ranges.

How can I include the start and end dates?

Change dates>$L$6 to dates>=$L$6 and dates<$L$7 to dates<=$L$7. If the cells include times, an end date entered at midnight will not include later times on that day.

Can I use a cell for the header search text?

Yes. Replace "Blue" in SEARCH with a cell reference, such as $L$5. Enter the text to find in L5; SEARCH will match it anywhere in each header.