SUMIFS in Excel with Dates: Examples and Tips

Formulas & functions · Updated November 20, 2026

To sum values for dates in Excel, use SUMIFS with the amount range first and the date range plus a date criterion after it. For a date range, use two criteria—one for the start date and one for the day after the end date—so every time on the final day is included.

How SUMIFS works with dates

SUMIFS adds numbers only when all its criteria are met. Its arguments begin with the range to add, followed by pairs of criteria ranges and criteria. A date criterion can match one exact date, or set a lower or upper boundary for a range. For example, a sales report might total amounts recorded on a particular day, in a calendar month, or between two dates.

Excel stores valid dates as serial numbers and displays them using a date format. A cell that looks like 1/12/2025 is therefore usually a number underneath, not a special kind of text. This is why a cell containing a real date works well as a criterion. It also explains a common problem: text that merely looks like a date may not match the real date values in the data.

In the examples below, assume dates are in A2:A100 and amounts are in B2:B100. The example records include $120 on January 5, 2025, $80 on January 12, and $200 on January 31. Those three records total $400 for January. Dollar signs are used only to make the example amounts easy to read; the formulas work with other numeric values too.

Match one exact date

To total entries from one exact day, put the date you want to match in a cell such as E2. With dates in A2:A100, amounts in B2:B100, and January 12, 2025 in E2, use:

=SUMIFS($B$2:$B$100,$A$2:$A$100,E2)

The result is $80 for the example records. The dollar signs in the ranges keep those references fixed if you copy the formula to another cell. You can also type a date into E2 by entering a valid date and formatting the cell as a date; the display format does not change the underlying value.

This exact-match approach is appropriate when the date column contains dates without times. If the cells contain timestamps, a value recorded at 9:00 a.m. is not equal to the date value for midnight. For that case, use a start-and-end interval instead of relying on exact equality.

Sum between two dates

A reliable way to include a date range is to use a greater-than-or-equal start boundary and a less-than end boundary. Set E2 to the first day to include and F2 to the day after the final day. With the same date and amount ranges, E2 set to January 1, 2025, and F2 set to February 1, 2025, enter:

=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&E2,$A$2:$A$100,"<"&F2)

The result is $400: the January 5, January 12, and January 31 records qualify, while February 1 does not. The ampersand joins the comparison operator to the value in the boundary cell, creating a criterion such as “greater than or equal to January 1.” Both criteria apply to the same date range, so a row must meet both boundaries to be counted.

Using an exclusive upper boundary—the first day after the range—is especially useful for timestamps. If the end boundary were “less than or equal to January 31,” a timestamp at 3:00 p.m. on January 31 would be greater than the date value representing midnight at the start of that day. The exclusive February 1 boundary includes all January 31 times while excluding February 1.

Build date criteria with DATE

You can create date boundaries directly in a formula with Excel’s DATE function. This avoids depending on how a typed date is interpreted under different regional date settings. With dates in A2:A100 and amounts in B2:B100, this formula totals January 2025:

=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&DATE(2025,1,1),$A$2:$A$100,"<"&DATE(2025,2,1))

It returns $400 for the sample January records. DATE takes the year, month, and day as separate numeric arguments, so the formula is not relying on whether a system interprets a typed date as month/day/year or day/month/year. It is also easy to audit: both boundaries are visible in the formula.

For a reusable report, cell references are usually easier to maintain than hard-coded dates. A user can change E2 and F2 without editing the formula. The DATE version is handy for a fixed reporting period or when you want the formula itself to make the intended period explicit.

Sum a calendar month dynamically

To total the month containing a date in E2, set E2 to any date in the desired month and use the first day of that month as the lower boundary. The following formula uses EOMONTH to find that month’s final date, then adds one to make the upper boundary exclusive. With dates in A2:A100 and amounts in B2:B100, and E2 set to January 15, 2025, use:

=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&EOMONTH(E2,-1)+1,$A$2:$A$100,"<"&EOMONTH(E2,0)+1)

The result is $400. The first boundary is January 1, and the second is February 1, so the formula includes every date in January regardless of the number of days in the month. It also works when E2 is in February, a leap-year February, or another month. EOMONTH is available in Microsoft 365 Excel; if Excel asks to enable the Analysis ToolPak when using it, check the installation or version rather than replacing the date logic with text matching.

An alternative is to put the first day of the month in E2 and use EDATE to calculate the next month’s first day. The EOMONTH approach above accepts any date within the month, which can be convenient in a report where the user selects a representative date.

Add more criteria alongside dates

SUMIFS can combine date criteria with other conditions. For instance, suppose salesperson names are in C2:C100, with Alex in C2, C4, and C5 for the three January records listed earlier. Those records have amounts of $120, $200, and $80, respectively, so Alex’s January total is $400 in this particular sample. To calculate the January total for the salesperson named in G2, with G2 containing Alex, use:

=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&E2,$A$2:$A$100,"<"&F2,$C$2:$C$100,G2)

Here E2 is January 1, F2 is February 1, and the amount, date, and salesperson ranges all cover rows 2 through 100. The result is $400 for the stated sample. Each criteria pair adds another condition, and all conditions must be true for a row’s amount to be included. This is an AND relationship, not an OR relationship.

For example, if you need the total for January or February, a single pair of date boundaries would cover both months; if you need one salesperson or another, you can add separate SUMIFS results or use another method to express the OR condition. Keep the ranges aligned: the sum range and every criteria range should have the same number of rows and corresponding row positions.

Handle timestamps and date-time values

A date-time value includes a fractional part for the time of day. For example, a record at 3:00 p.m. on a date has a value later than midnight on that date. To sum all transactions on the date in E2, use a lower boundary at the date and an exclusive upper boundary one day later. With timestamps in A2:A100, amounts in B2:B100, and E2 set to January 12, 2025, use:

=SUMIFS($B$2:$B$100,$A$2:$A$100,">="&E2,$A$2:$A$100,"<"&E2+1)

If the sample has a $100 transaction at 9:00 a.m. and a $75 transaction at 3:00 p.m. on January 12, and a $40 transaction just after midnight on January 13, the result is $175. The next day’s record is excluded. The same logic can be extended to longer ranges by using the day after the final day as the upper boundary.

Formatting a cell to show only a date does not remove its time component. If an exact-date formula returns zero even though the displayed dates appear identical, check whether one side contains a time. The interval formula is generally safer for timestamp data because it deliberately covers every time during the intended day.

Common date-matching problems

  • Dates stored as text: If imported values are text strings, Excel may not compare them as date serial numbers. Re-import or convert the values to real Excel dates before relying on date criteria. Changing the display format alone does not convert text into dates.
  • Hidden times: A date-time may display only its date. Use a range from the date up to, but not including, the next day when you need to include all times.
  • Wrong boundary: A “less than or equal to” condition for a date-only end value can omit times later that day. Prefer a less-than condition against the next day.
  • Inconsistent ranges: A sum range and criteria ranges must correspond row by row and have matching dimensions. Check that none starts on row 2 while another starts on row 3.
  • Regional date interpretation: A typed value such as 04/05/2025 can be ambiguous across regional settings. Use a cell containing a verified date or construct the boundary with DATE(year,month,day).
  • Unexpected blanks or errors: Check that date cells are truly numeric dates and that the intended amount cells contain numbers. Also check for extra spaces or inconsistent labels when an additional text criterion is involved.

When troubleshooting, select a date cell and inspect the formula bar and number format, then test a small subset of rows. Sorting or filtering the source data by date can help reveal whether values include times or are stored as text. Avoid assuming that two cells match just because they look alike on the worksheet.

SUMIFS compared with other approaches

SUMIFS is a good fit when you want one total and the conditions are straightforward. It is easier to read than manually adding many individual cells, and extra conditions such as a region, product, or salesperson can be added as criteria pairs. It does not require the date range to be sorted.

SUMIF handles one condition, whereas SUMIFS supports multiple conditions and uses a different argument order: SUMIFS puts the sum range first. For date ranges, two date criteria are normally needed, which makes SUMIFS the more direct choice. A PivotTable is often preferable when readers need to explore totals by month, category, and other fields interactively. For a fixed report cell that should calculate one date interval, SUMIFS is usually simpler.

Do not use a text function such as TEXT to turn dates into month names as a shortcut unless the task specifically requires text labels. Text comparisons can be affected by spelling, language, and formatting, while date boundaries compare the underlying date values and work naturally across years.

Frequently Asked Questions

Can SUMIFS match a date range?
Yes. Use one criterion for dates greater than or equal to the start and another for dates less than the day after the end. This includes the whole final day and works well with date-time entries.

Why does SUMIFS return zero for a date that looks correct?
The source value may be text, or it may contain a time that prevents an exact match. Convert text to real Excel dates, or use a start-of-day to next-day interval for timestamps. Also check that the sum and criteria ranges align.

How do I sum a whole month with SUMIFS?
Use the first day of the month as the inclusive lower boundary and the first day of the next month as the exclusive upper boundary. You can enter those dates in cells or generate them with date functions.

Can SUMIFS use dates typed directly in the formula?
Yes, but constructing dates with DATE(year,month,day) avoids ambiguity from regional date formats. Cell references are also useful because they let someone change the reporting period without editing the formula.

Does SUMIFS include the end date?
It depends on the criterion. A less-than-or-equal comparison includes the date value at midnight but can miss later times on that date. For a complete end date, use a less-than comparison against the following day.

Can I use SUMIFS for dates and a salesperson at the same time?
Yes. Add a date criteria pair and a salesperson criteria pair. The row must satisfy every condition, so the result is the total for that salesperson within the selected date interval.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.