How to Sum Excel Values by Keyword and Date Range
Question details
The user needs to calculate total values in a spreadsheet based on a specific keyword in one column and a specific date range in another column.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summarizing operational or financial data for specific categories over a selected time period, such as calculating monthly totals.
- Observed behavior
- Users require a reliable method to sum values while avoiding locale-sensitive text date calculation errors and unexpected spilled array results.
Ensure your date column contains valid Excel serial dates rather than text strings, as text-based dates will cause formula calculation errors.
Use the SUMIFS Function with Cell References
The most robust way to sum by keyword and date range is using the SUMIFS function referencing actual date cells.
Embedding hardcoded dates into formulas can cause errors if the file is opened on a computer with different regional date settings. Using dynamic cell references and the EDATE function is much safer.
Dedicate a specific cell for your start date. For example, type the first day of the month (e.g., 01/01/2024) into cell E1.
Select the cell where you want the total to appear. Enter the formula: =SUMIFS(B:B, C:C, "*keyword*", A:A, ">="&E1, A:A, "<"&EDATE(E1,1)). In this example, column B contains the values to sum, column C contains the text keywords, and column A contains the dates.
By placing asterisks around your keyword (like "*keyword*"), the formula will sum any cell in column C that contains that specific word, regardless of other text.
Press Enter to calculate the total. If the result spills unexpectedly into multiple cells, check whether you are editing an array formula inside an Excel Table.

Summarize Data Using a PivotTable
A PivotTable is excellent for summarizing consolidated data by month and category without writing complex formulas.
Use WPS Spreadsheet for Advanced Formulas and PivotTables
WPS Spreadsheet provides powerful data analysis tools, fully supporting complex SUMIFS formulas, dynamic arrays, and advanced PivotTables. It is a highly compatible and lightweight solution for all your spreadsheet needs.
- 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the data.
- 2. Insert formulas effortlessly: Use the built-in formula builder to insert your SUMIFS formula accurately with step-by-step argument prompts.
- 3. Create dynamic summaries: Navigate to the Insert tab to create a PivotTable in seconds for codeless data analysis.

Frequently Asked Questions
Why is my SUMIFS formula returning a zero for dates?
This usually happens when the dates in your criteria or data range are stored as text rather than valid Excel date serial numbers. You can fix this by selecting the date column, navigating to the Data tab, selecting Text to Columns, and clicking Finish to convert them.
How do I sum using a partial keyword match?
You can use wildcard characters in your SUMIFS criteria. For example, use "*open*" to sum any cells that contain the word 'open' regardless of what other text surrounds it in the cell.
Why does my formula result in a #SPILL! error?
A #SPILL! error occurs when a dynamic array formula returns multiple results, but there is existing data blocking the output range. Clear the cells below or to the right of your formula to allow the results to populate properly.
Can I use PIVOTBY instead of a PivotTable?
Yes. If you are using a modern spreadsheet version that supports dynamic arrays, the PIVOTBY function can summarize your data by row and column directly within a formula, eliminating the need to manually refresh a traditional PivotTable.




