How to Use Dates in SUMIFS Formulas in Microsoft Excel

Formulas & functions · Updated November 20, 2026

To sum values for a date or date range in Excel, use SUMIFS with date criteria such as ">="&DATE(2026,3,1) and "<"&DATE(2026,4,1). The first criterion sets the start of March and the second stops at the start of April, so all March dates are included. SUMIFS is useful for totaling sales, hours, expenses, or other values by day, month, or period without changing your source data.

What You’ll Need

  • A worksheet with a column of real Excel dates and a corresponding column of numbers to add. In the examples below, dates are in A2:A100 and amounts are in B2:B100.
  • Optional cells for the dates you want to use as criteria. The examples use E2 for a start date and F2 for an end date.
  • Microsoft Excel for Microsoft 365 on Windows. The SUMIFS and DATE functions used here are also available in many earlier Excel versions. On Mac, the formulas work the same way; ribbon paths may look slightly different.

Step-by-Step

  1. Set up the source data. Keep each transaction or record on its own row. Put the date in one column and the value to total in another. For example, A2:A100 can hold transaction dates and B2:B100 can hold sales amounts. Make sure each amount is on the same row as its date. SUMIFS checks corresponding rows, so misaligned or separately sorted columns can produce a plausible but incorrect total.

    Excel stores a genuine date as a number, even though it displays it in a familiar format such as 3/5/2026. To check a date, select a cell and temporarily choose Home > Number Format > General. A date serial number indicates a numeric date; text may remain unchanged or display as text. Restore a date format afterward if needed. The display format alone does not determine whether Excel can use the value as a date criterion.

  2. Choose the date or period to summarize. For a reusable calculation, enter a start date in E2 and an end date in F2, then format those cells as dates using Home > Number Format. Enter the dates as actual Excel dates, not as text surrounded by quotation marks. For example, entering 3/1/2026 in E2 and 3/31/2026 in F2 gives you a March reporting period. If your regional settings interpret dates differently, use the calendar picker where available or enter a date with DATE in a formula to avoid ambiguity.
  3. Sum records for one exact date. Use an exact-match criterion when your source values contain dates but no time-of-day information. With dates in A2:A100, amounts in B2:B100, and the date to match in E2, enter =SUMIFS(B2:B100,A2:A100,E2). If the matching rows contain amounts of 80 and 45, the formula returns 125. This is a practical choice for a daily total that updates when you change E2.
  4. You can also build the criterion with DATE rather than typing a date string. With dates in A2:A100 and amounts in B2:B100, this formula sums entries for March 5, 2026: =SUMIFS(B2:B100,A2:A100,DATE(2026,3,5)). If the source has two entries on that date for 80 and 45, the result is 125. DATE takes year, month, and day as separate numbers, which avoids confusion between regional date orders such as month/day/year and day/month/year.

  5. Sum values between a start and end date. SUMIFS can apply more than one condition to the same date column. With dates in A2:A100, amounts in B2:B100, the first date in E2, and the last date in F2, enter =SUMIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2). The formula includes both boundary dates. If E2 is March 1, F2 is March 31, and the matching amounts are 120, 80, 45, and 200, it returns 445. The two date-range conditions are joined with AND: a row must meet both to be added.

    The comparison operators are text criteria, so join each operator to its date with the ampersand. In an Excel formula, the less-than and greater-than symbols go inside straight double quotes. The &amp; shown by an HTML page represents the ampersand character in the formula; when you type it in Excel, use the ordinary & character.

  6. Use a half-open range when source dates may include times. A date-time value such as March 31 at 2:00 p.m. is greater than the date-only value March 31 at midnight. For that reason, a condition of less than or equal to the end date can leave out records later on that day. With dates or timestamps in A2:A100, amounts in B2:B100, a start date in E2, and an end date in F2, use =SUMIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<"&F2+1). The end boundary is exclusive: it includes everything before midnight after F2, including times on the end date.

    For example, if E2 is March 1 and F2 is March 31, this method includes a March 31 entry at 2:00 p.m. but excludes an April 1 entry. If F2 is a date-time rather than a date-only value, decide whether the end time itself should be included and set the boundary accordingly.

  7. Summarize a whole month from any date in that month. A month can have 28 to 31 days, so using a hard-coded final day makes a formula less reusable. With dates in A2:A100, amounts in B2:B100, and any date in the target month in E2, enter =SUMIFS(B2:B100,A2:A100,">="&DATE(YEAR(E2),MONTH(E2),1),A2:A100,"<"&DATE(YEAR(E2),MONTH(E2)+1,1)). If E2 contains March 12, 2026, it sums dates from March 1 up to, but not including, April 1. With the example March amounts of 120, 80, 45, and 200, it returns 445. It also works for December: DATE rolls the next-month boundary into January of the following year.

  8. Add another condition only if you need it. SUMIFS accepts additional range-and-criteria pairs, such as a region, product, or employee. With dates in A2:A100, amounts in B2:B100, regions in C2:C100, and the region name in G2, this example sums that region’s sales on the date in E2: =SUMIFS(B2:B100,A2:A100,E2,C2:C100,G2). Each criteria range must cover the same rows as the sum range. Change E2 or G2 to reuse the calculation for another date or region.
  9. Check the result against a small sample. Filter the source data to the period and category in your formula, then add a few matching amounts manually. For a March 1 through March 31 total of 445, verify that the four sample amounts—120, 80, 45, and 200—are included and that April 1’s 90 is excluded. A quick comparison is especially helpful before copying a formula into a report.

Common Mistakes

  • Using text that looks like a date. A cell containing the text “3/5/2026” may not match a real Excel date with the same display. Convert imported text dates into numeric dates before relying on them in SUMIFS. Changing a cell’s number format is not always enough to convert text.
  • Leaving out the ampersand. A comparison criterion must combine its operator and date, as in ">="&E2. Typing ">=E2" asks Excel to compare against the literal text E2 instead of the date stored in that cell.
  • Including only one boundary. A start-date condition alone includes every later date, and an end-date condition alone includes every earlier date. Use both criteria against the date column when you need a bounded period.
  • Using the wrong end-date logic with times. The criterion "<="&F2 includes the exact date serial at midnight, but may exclude times later that day. Use a less-than next-day boundary when the source contains timestamps.
  • Using ranges of different sizes. The sum range and every criteria range should start and end on matching rows. For example, B2:B100 paired with A2:A99 can cause an error or an unreliable setup.
  • Typing a date in a locale-sensitive form. An entry such as 4/5/2026 can mean April 5 or May 4, depending on regional settings. Use a date cell whose value you can inspect, or construct fixed dates with DATE(year,month,day).

Tips

  • Use cell references for start and end dates so you can change the reporting period without editing a long formula. Clear labels such as “Start date” and “End date” beside E2 and F2 help prevent accidental swaps.
  • For reusable reports, convert the data range into an Excel table with Insert > Table. Tables expand as rows are added. If you use structured references in a formula, check that the date and amount column names match your table headings exactly.
  • Use the half-open interval pattern—greater than or equal to the first date and less than the day after the final date—when time stamps may be present. It is also safe for date-only values.
  • Keep dates and numbers in separate columns. A cell that combines a date and an amount, or stores both as text, is harder to summarize reliably with SUMIFS.
  • If you copy a formula down a report, consider whether the criteria cells should move. Add dollar signs to lock a reference such as $E$2 when you want every copied formula to use the same date. Leave it relative when each row should refer to a different date cell.
  • Use commas between formula arguments in the examples. Some Excel regional settings use semicolons instead; if Excel flags the commas as a syntax error, use the list separator configured for your installation while keeping the formula logic unchanged.

Troubleshooting

The formula returns zero, but matching rows appear to exist. First, test whether the date cells are numeric dates rather than text. Select one source date, change its format temporarily to General, and inspect the displayed value. If it remains text, convert the column using Data > Text to Columns when appropriate, or use a reliable conversion method for the imported format. Also confirm that the criterion date is a real date and that it falls within the source dates.

The total is too small by a few records. Check for times attached to dates. A value displayed as 3/31/2026 may internally be 3/31/2026 14:00. If your formula uses an inclusive end date, replace the upper criterion with a less-than comparison to the day after the end date. Also check that no rows have been sorted independently in one of the criteria columns.

The formula returns an error. Verify that each range has the same number of rows, that parentheses and quotation marks are paired, and that each SUMIFS argument appears in the right order: sum range, criteria range, criterion, followed by any additional range-and-criterion pairs. Confirm that the comparison operator is inside straight quotation marks, not curly quotation marks copied from formatted text. If commas are rejected, your regional list separator may be a semicolon.

The total includes the wrong dates. Inspect E2 and F2 individually, including the year, month, and day. Remember that the usual bounded-range formula includes both dates, while the timestamp-safe version includes the start date and everything before the day after the end date. If the dates are entered as ambiguous text, replace them with unambiguous date values.

New records are missing. Check whether the formula ranges stop before the new rows. Extend the sum and criteria ranges together, or use an Excel table so its data range can expand as you add records. Avoid extending only the amount range: every criteria range must include the corresponding new rows too.

Frequently Asked Questions

Can SUMIFS match one exact date?
Yes. Use the date cell as a criterion, or use DATE(year,month,day) for a fixed date. The source date and criterion need to be genuine Excel date values. If source cells also contain times, use a start-of-day and next-day boundary instead of an exact match.

How do I sum a whole month without typing its last day?
Set the lower boundary to the first day of the chosen month and the upper boundary to the first day of the following month. Use greater than or equal to the first boundary and less than the second. This handles different month lengths and works with timestamps.

Does SUMIFS include the start and end dates?
With criteria of greater than or equal to the start and less than or equal to the end, both date boundaries are included. If times appear in the source, use less than the day after the end date instead; that includes all times on the stated final date.

Why does formatting a text date as Date not fix the formula?
Number formatting changes how a value is displayed; it does not necessarily convert text into an Excel date serial. Convert the underlying text to a real date value, then apply the date format you want.

Can I use dates and another condition in the same SUMIFS?
Yes. Add another criteria-range-and-criterion pair for each condition, such as region, product, or employee. The ranges must align row for row, and all criteria in one SUMIFS must be true for a record to contribute to the total.

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.