Function Problems

How to Sum Excel Values by Keyword and Date Range

Huma Ashraf ChHuma Ashraf Ch Nov 10, 2026 869 views

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.

How to Sum Excel Values by Keyword and Date Range
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.
Before you start

Ensure your date column contains valid Excel serial dates rather than text strings, as text-based dates will cause formula calculation errors.

Solution 1Recommended

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.

1
Set up your criteria cells

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.

2
Enter the SUMIFS formula

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.

3
Use wildcards for partial matches

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.

4
Evaluate the result

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.

Use the SUMIFS Function with Cell References
Automated Month Calculation: Using the EDATE(E1, 1) function automatically identifies the exact same day in the following month. This ensures accurate monthly totals regardless of how many days are in the current month.
Efficient Data Analysis with WPS

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. 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the data.
  2. 2. Insert formulas effortlessly: Use the built-in formula builder to insert your SUMIFS formula accurately with step-by-step argument prompts.
  3. 3. Create dynamic summaries: Navigate to the Insert tab to create a PivotTable in seconds for codeless data analysis.
Fully compatible with Microsoft Excel formulas like SUMIFS and EDATE.Robust PivotTable features for effortless monthly data summarization.Free, lightweight, and features a familiar user interface.Built-in error checking to help resolve spilled array or text-date issues.
microsoft office alternative - wps office

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.