SUMIF Formula for Dates: Examples and Common Fixes

Formulas & functions · Updated November 20, 2026

Use Excel’s SUMIF with a date as the criterion to add values for a specific date; use comparison criteria such as “on or after” and “before” to sum a date range. For example, if dates are in A2:A6 and amounts are in B2:B6, =SUMIF(A2:A6,DATE(2025,1,15),B2:B6) totals the amounts recorded on January 15, 2025.

How SUMIF works with dates

SUMIF adds numbers from one range when the corresponding cells in another range meet a condition. Its argument order is SUMIF(range, criteria, [sum_range]): range contains the values to test, criteria describes which ones to include, and sum_range contains the values to add. The last argument is optional; when omitted, Excel sums the tested range itself.

Excel stores a valid date as a number, with a date format controlling how that number appears on screen. A cell displayed as Jan 15, 2025 therefore contains a numeric date value that SUMIF can compare. The format is not what makes a date usable: a cell containing text that merely looks like a date may not match a genuine Excel date.

In a typical transaction table, the date column is the criteria range and the amount column is the sum range. Keep both ranges the same size and aligned by row. If the dates are in A2:A100, for example, the corresponding amounts should be in B2:B100, not in a range beginning on a different row.

Sum values for one exact date

For an exact match, give SUMIF a date value as its criterion. The DATE function is a dependable way to specify a date directly because its arguments are year, month, and day; it avoids ambiguity between regional date formats such as 1/4/2025.

Assume A2:A6 contains Jan 5, Jan 15, Jan 15, Jan 20, and Feb 1, 2025, while B2:B6 contains 120, 80, 45, 200, and 90. With those dates in A2:A6 and amounts in B2:B6, use =SUMIF(A2:A6,DATE(2025,1,15),B2:B6). It returns 125, because the two January 15 rows contain 80 and 45, and 80 + 45 = 125.

You can also refer to a date entered in a worksheet cell. That is useful when a report user needs to change the target date without editing the formula. Enter a real Excel date in D1. With dates in A2:A6, amounts in B2:B6, and the target date in D1, use =SUMIF(A2:A6,D1,B2:B6). If D1 is January 15, 2025, the result is 125 for the example data above.

Sum dates within a month or other range

To include dates from the start of a period through its end, use two comparisons. SUMIF accepts one condition, so a formula with two boundaries is usually written with SUMIFS, which supports multiple criteria. For a month, a reliable pattern is “date greater than or equal to the first day, and date less than the first day of the next month.”

With dates in A2:A6 and amounts in B2:B6 as in the example above, use =SUMIFS(B2:B6,A2:A6,">="&DATE(2025,1,1),A2:A6,"<"&DATE(2025,2,1)) to total January 2025. The result is 445: the January amounts are 120, 80, 45, and 200, which add to 445. February 1 is excluded because the second condition is strictly less than that date.

This end-exclusive pattern is especially helpful when a date column may contain times as well as dates. A timestamp such as Jan 31 at 3:00 p.m. is later than the date value for Jan 31 at midnight. Using a condition of “less than February 1” includes all times on January 31, whereas a “less than or equal to January 31” boundary could miss them.

Use SUMIF for a start date or a rolling period

A single comparison can answer questions such as “How much was recorded on or after this date?” Concatenate the comparison operator with the date value so Excel reads the full condition as criteria. Keep the operator inside quotation marks; the date itself can be supplied by DATE or a cell reference.

With dates in A2:A6 and amounts in B2:B6, use =SUMIF(A2:A6,">="&DATE(2025,1,15),B2:B6) to sum entries on or after January 15, 2025. For the example data, the result is 415: the qualifying amounts are 80, 45, 200, and 90. To sum entries before a cutoff instead, use the less-than operator and the cutoff date.

For a rolling total that changes each day, TODAY can provide the current date. With dates in A2:A100 and amounts in B2:B100, =SUMIF(A2:A100,">="&TODAY()-29,B2:B100) totals records dated from 29 days ago through today, assuming the date cells contain no time values. That span is 30 calendar dates when both endpoints are included. If the data contains future dates, add an upper boundary with SUMIFS to exclude them; a fixed reporting period may be clearer when a report needs to be reproducible later.

Build a reusable monthly formula

Hard-coding the year and month works for a one-off calculation, but a worksheet can make the reporting period easy to change. Put any date within the month being reported in D1, then derive the first day of that month and the first day of the following month in the criteria. This handles months of different lengths and year changes without manually entering the last day.

With dates in A2:A100, amounts in B2:B100, and a date from the desired month in D1, use =SUMIFS(B2:B100,A2:A100,">="&DATE(YEAR(D1),MONTH(D1),1),A2:A100,"<"&DATE(YEAR(D1),MONTH(D1)+1,1)). If D1 is any date in January 2025 and the five example rows are in the ranges, the result is 445. DATE correctly rolls a month value of 13 into January of the next year, so the month-end calculation also works for December.

For a summary table with one month per row, put a date from each month in the summary and copy the formula down after adjusting the reference to that row’s month cell. Use relative references when the month cell should change as the formula is copied. Use dollar signs to lock a fixed data range, such as $A$2:$A$100 and $B$2:$B$100, if copying the formula would otherwise shift the source ranges.

SUMIF or SUMIFS for date criteria?

Choose SUMIF when one condition is enough: an exact date, dates on or after a cutoff, or dates before a cutoff. Use SUMIFS when a total needs two date boundaries or when date filtering is combined with another condition, such as a department, category, or status. The argument order differs: SUMIF puts the criteria range and criteria before the sum range, while SUMIFS starts with the sum range and then takes criteria-range, criteria pairs.

For instance, a monthly total for one product needs both a date interval and a product match. With dates in A2:A100, product names in B2:B100, amounts in C2:C100, and the chosen product in E1, use =SUMIFS(C2:C100,A2:A100,">="&DATE(2025,1,1),A2:A100,"<"&DATE(2025,2,1),B2:B100,E1). If the January rows for the product named in E1 total 300, the formula returns 300. SUMIF cannot express both date limits and the product condition in one call.

Common date problems and fixes

If a formula returns zero when matching dates seem present, check the underlying values before changing the formula. A date displayed in the same style may be stored as text, the criterion may contain a time component, or the criteria and sum ranges may be misaligned. Sorting or formatting a column does not by itself convert text to genuine dates.

  • Check for text dates: Select a suspect cell and inspect the formula bar or try changing its number format. If it remains text or does not behave like a date in calculations, convert the source data. Depending on the input, Data > Text to Columns can help parse text dates; confirm the date order is interpreted correctly before accepting the conversion.
  • Check for hidden times: A cell formatted to show only a date can still contain a time. An exact SUMIF date match compares the complete stored value, so midnight on a date does not equal that date at noon. Use a start-inclusive and next-day-exclusive SUMIFS interval to total a full day.
  • Check locale and criteria construction: A typed text criterion such as “1/2/2025” can be interpreted differently in different regional settings. DATE(year,month,day) or a cell containing a verified date avoids that ambiguity. For operators, concatenate the operator with the date value rather than typing an incomplete comparison.
  • Check the ranges: The criteria range and sum range should cover corresponding rows and the same number of cells. Also verify that the amount cells contain numbers, not numeric text, and that the correct year is included in the date range.

When imported dates are inconsistent, fix the source column rather than repeatedly compensating in the formula. Keep a clean date column as actual date values and, if needed, a separate display column for labels. This makes sorting, filtering, charting, and date-based calculations more predictable.

Frequently Asked Questions

Can SUMIF sum a whole month?
Yes, but two month boundaries are needed. Use SUMIFS with a date greater than or equal to the first day of the month and less than the first day of the next month. This also includes timestamps throughout the final day.

Why does SUMIF return zero for a date that looks right?
The cell may contain text instead of an Excel date, include an unseen time, or represent a different year than the criterion. Check the cell value and make sure the criteria range aligns with the amount range.

Can I use a cell reference as the date criterion?
Yes. Put a valid Excel date in a cell and use that cell as the SUMIF criterion for an exact match. For greater-than or less-than conditions, join the quoted operator to the cell reference with the ampersand operator.

Does SUMIF include the date in a greater-than-or-equal test?
Yes. The >= operator includes values exactly equal to the criterion date as well as later dates. Use > when the date itself should be excluded.

Should I use SUMIF or SUMIFS for a date interval?
Use SUMIFS for a start and end date because it can apply both conditions to the same date range. SUMIF is suitable when there is only one date condition.

Do I need to format the result as a date?
No. A sum of amounts is numeric, so format the result as currency, a number, or another appropriate amount format. Date formatting is for displaying date values, not for making the sum calculation work.

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.