Sum Rows in Excel Columns with Matching Text Headers
Question details
Sum values from columns whose headers contain specified text, using only rows within a selected date range.

- 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.
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.
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.
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.
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))
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.

Sum a fixed block of rows by matching headers
Use this shorter formula when the row boundaries are fixed and no date filter is needed.
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. Open your workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 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. 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. 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.

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.




