Formula Errors

How to Use SUMIFS with Date Ranges and Criteria in Excel

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to construct a SUMIFS formula in Excel to total values based on a specific text category and a defined date range (start and end dates).

Product: Excel
Device & OS: not provided
Scenario: Calculating a conditional sum using multiple criteria, specifically filtering rows that match a certain text category and fall within a specific month's start and end dates.
Observed behavior: The goal is to correctly output the total sum for matching rows without duplicating the sum range in the formula or causing a formula error.

Before you start

Ensure your date columns are formatted as Dates and your values to sum are formatted as Numbers in Excel to prevent calculation errors.

Solution 1Recommended

Use the SUMIFS Function with DATE Functions

This method uses the standard SUMIFS formula combined with the DATE function to securely filter by a category and a start-and-end date range.

When using SUMIFS for date ranges, use the concatenation operator (&) to join logical operators (">=", "<=") with the DATE function. This ensures Excel interprets the date boundaries correctly regardless of regional date settings. Remember that the sum range is always the first argument and should only be listed once.

Select the result cell

Click on the empty cell where you want the total sum to appear.

Input the SUMIFS formula

Type `=SUMIFS(F2:F500, H2:H500, "Branding", A2:A500, ">="&DATE(2025,1,1), A2:A500, "<="&DATE(2025,1,31))`.

Adjust the cell references

Change `F2:F500` to your actual sum range, `H2:H500` to your category column, and `A2:A500` to your date column. Replace "Branding" with your target category.

Execute the formula

Press Enter to calculate the result.

Syntax Reminder: Unlike SUMIF, the SUMIFS function requires the sum range (e.g., F2:F500) to be the very first argument. Do not repeat the sum range as a criteria range.

WPS Spreadsheet Formula Support

Calculate Conditional Sums Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced Excel functions, including SUMIFS with complex date criteria and text filtering. You can perform these data analysis calculations seamlessly using identical formulas.

1. Open your data file: Launch WPS Spreadsheet and open your existing Excel workbook.
2. Enter the formula: Select a cell and input the SUMIFS formula using the identical syntax: `=SUMIFS(sum_range, criteria_range1, criteria1, ...)`.
3. Press Enter: Hit Enter to instantly view your calculated total based on the date range and category.

Fully compatible with Microsoft Excel formulas and the .xlsx file format.Intelligent formula auto-completion and syntax highlighting to prevent errors.Free and lightweight alternative for powerful data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my SUMIFS formula return 0 when using date ranges?

This usually happens if your dates are formatted as text instead of actual Excel serial dates. Ensure your date column is formatted as Date, and use the DATE(year, month, day) function in your criteria instead of typing dates as plain text.

Can I use cell references instead of hardcoding dates in the SUMIFS formula?

Yes. Instead of typing `">="&DATE(2025,1,1)`, you can put your start date in a cell like B1 and use `">="&B1` as the criterion in your SUMIFS formula.

What is the difference between SUMIF and SUMIFS?

SUMIF is designed for a single condition, and its sum range is placed as the last argument in the formula. SUMIFS allows multiple conditions (such as a category plus a date range), and its sum range must be placed as the very first argument.

Excel & Data Problems