Excel SUMIF Between Dates: Multiple Criteria Explained

Formulas & functions · Updated November 19, 2026

To sum values in Excel for Microsoft 365 that fall between two dates and meet other conditions, use SUMIFS, not SUMIF: it can apply both date boundaries and additional criteria. For example, use a start-date test of greater than or equal to the first date and an end-date test of less than the day after the last date; this includes every time on the final day.

Why SUMIFS is the right function

SUMIF adds values that meet one condition. A date interval requires two conditions on the date column: the date must be on or after the start and on or before the end. If you also need a matching department, product, customer, or status, that is another condition. SUMIFS is designed to sum values while checking several criteria at once.

The argument order is important. In SUMIFS, the first argument is the range to add; after that, supply a criteria range and its criterion for each condition. In contrast, SUMIF takes the criteria range before its sum range. This difference is a frequent cause of formulas that look plausible but add the wrong cells.

Think of a date range as two separate tests rather than one special “between” operator. Excel checks that each row passes both tests, then adds the corresponding value. Criteria in SUMIFS are combined as AND conditions: every listed test must be true for a row to count.

Set up the data and date boundaries

Assume a transaction list has dates in A2:A6, regions in B2:B6, and amounts in C2:C6. The rows are: January 5, East, 120; January 15, East, 80; January 15, West, 50; January 31, East, 200; and February 1, East, 70. The dates must be real Excel dates, not text that merely looks like a date.

Put the first date of the period in E2 and the last date in F2. For example, enter January 10, 2025 in E2 and January 31, 2025 in F2. Format those cells as dates if needed; cell formatting changes how a value is displayed, not whether Excel recognizes it as a date. You can type dates in a format Excel recognizes in your regional settings, or build them with DATE.

For a period from January 10 through January 31, the East-region total is 280: the qualifying amounts are 80 and 200. The West transaction does not match the region, and the February 1 transaction is outside the period.

Use an inclusive date range with another criterion

With dates in A2:A6, regions in B2:B6, amounts in C2:C6, the start date in E2, and the end date in F2, this formula sums East transactions from the start date through the end date. The result for the sample rows is 280.

=SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<"&F2+1,B2:B6,"East")

The two date criteria use the same date range, A2:A6. The first criterion includes dates greater than or equal to E2. The second includes dates strictly earlier than F2 plus one day. For date-only data, this is equivalent to including dates through F2. It also handles timestamps correctly: a transaction at 4:30 p.m. on January 31 is less than February 1, even though it is greater than midnight at the start of January 31.

The ampersand joins a comparison operator, written as text, to a date value. Do not put the cell reference inside the quoted text: ">="&E2 builds a criterion Excel can compare with the value in E2. If E2 or F2 is blank, the result may not reflect the range you intended, so make sure both boundary cells are filled.

Write the dates directly in the formula

If you do not need editable date cells, use DATE(year,month,day) to construct date values. This avoids ambiguity between regional date formats such as month/day/year and day/month/year. With dates in A2:A6, regions in B2:B6, and amounts in C2:C6, the following sums East transactions from January 10 through January 31, 2025. The sample result is 280.

=SUMIFS(C2:C6,A2:A6,">="&DATE(2025,1,10),A2:A6,"<"&DATE(2025,2,1),B2:B6,"East")

Notice that the upper bound is February 1, not January 31. That is intentional: the less-than test includes the whole of January 31, including times later than midnight. If the data is guaranteed to contain dates with no time component, a less-than-or-equal-to test against January 31 can work; the exclusive next-day boundary is more robust and easier to reuse with timestamped records.

Add more criteria to the same date test

Every additional condition is another criteria-range and criterion pair. For example, suppose D2:D6 contains transaction status and you want only rows marked “Paid.” Add D2:D6 and the status criterion after the region pair. The sum range and all criteria ranges should cover corresponding rows.

If the desired region is stored in G2 rather than typed into the formula, use the cell as the criterion. With the same dates, regions, and amounts in A2:A6, B2:B6, and C2:C6, E2 as the start date, F2 as the end date, and G2 containing East, the result remains 280.

=SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<"&F2+1,B2:B6,G2)

This makes the summary reusable: change G2 to West or change either date boundary without editing the formula. To add a product condition, for instance, supply the product range and a product value as one more pair. Criteria ranges should be the same size and shape as the sum range; mismatched ranges can produce errors or misleading results.

Sum a month or another changing period

For a monthly report, use the first day of the month as the lower boundary and the first day of the following month as the exclusive upper boundary. This avoids calculating the number of days in each month and works for February, leap years, and months of different lengths. If E2 contains January 1, 2025, then the East total for January in the sample is 400: 120 + 80 + 200.

With dates in A2:A6, regions in B2:B6, amounts in C2:C6, and the first day of the target month in E2, this formula uses EDATE to find the next month’s first day. Its result for the sample data is 400.

=SUMIFS(C2:C6,A2:A6,">="&E2,A2:A6,"<"&EDATE(E2,1),B2:B6,"East")

Enter a genuine first-of-month date in E2, then copy the formula for other months by changing that cell. If you want a custom interval rather than a calendar month, use separate start and end cells as in the earlier example. Be clear whether report boundaries are inclusive; a range defined as January 10 through January 31 is not the same as a range ending at the start of January 31.

SUMIFS compared with SUMPRODUCT

For ordinary date, category, and amount criteria, SUMIFS is usually the clearest choice. It expresses each test directly, is straightforward to audit, and is designed for this type of conditional sum. SUMPRODUCT can also multiply logical tests and add the matching amounts, which can be useful when a calculation needs array-style logic that is awkward to express with SUMIFS.

With dates in A2:A6, regions in B2:B6, amounts in C2:C6, start date E2, and end date F2, this SUMPRODUCT formula returns 280 for East from January 10 through January 31.

=SUMPRODUCT((A2:A6>=E2)*(A2:A6<F2+1)*(B2:B6="East")*C2:C6)

Each comparison produces a set of TRUE or FALSE results; multiplication treats those as 1 or 0, leaving amounts only where every condition is true. For a straightforward report, prefer SUMIFS because its criteria are easier to read and adjust. With large data sets, avoid unnecessarily complex calculations and test performance in the actual workbook.

Common errors and how to fix them

  • Dates are stored as text. A cell can display “Jan 15” while containing text rather than a date serial value. Check whether Excel recognizes it as a date, and convert imported text dates before relying on comparisons.
  • The end date misses late-day records. A criterion of less than or equal to the end date can exclude timestamps later than midnight on that date. Use a less-than test against the day after the end date.
  • Criteria syntax is incomplete. When the boundary is in a cell, join the operator and cell with ampersand, as in ">="&E2. A bare text criterion that does not include the cell value will not test the intended boundary.
  • Ranges have different sizes. Keep the sum range and every criteria range aligned, such as C2:C100 with A2:A100 and B2:B100. If one starts on row 1 or ends on row 101, the criteria no longer correspond to the same records.
  • Dates or amounts are outside the selected range. If new records are appended below row 6, a formula ending at row 6 will not include them. Extend the ranges or use an Excel Table, whose structured references can expand as rows are added.
  • The total is zero unexpectedly. Check for extra spaces or inconsistent labels in the category column, confirm the start date is not after the end date, and verify the values in the sum range are numeric.

Excel formulas use commas in the examples above. Some regional settings use semicolons as argument separators, so the displayed formula may need that separator change in those installations. The SUMIFS function and its logic are the same in Excel for Microsoft 365 on Windows, Mac, and the web, although regional settings can affect how formulas are entered.

Frequently Asked Questions

Can I use SUMIF for a date range?
Not by itself for both boundaries: SUMIF supports one criterion. Use SUMIFS with a lower-date and upper-date criterion to cover a range, then add more criteria pairs if required.

How do I sum between two dates and match a name?
Use the date range twice in SUMIFS, once for the start and once for the end, then add the name range and the requested name as another criteria pair. Keep all ranges aligned with the amounts being summed.

Should the end-date condition use < or <=?
For dates that may include times, use “less than the day after the end date.” It includes every time on the selected end date. A less-than-or-equal-to condition against the end date can exclude later times that same day.

Why does SUMIFS return zero when the dates look correct?
They may be text rather than real Excel dates, the formula may point at the wrong rows, or the date criteria may be joined incorrectly. Also check that matching category labels have no extra spaces and that the sum values are numeric.

Can the date range come from cells?
Yes. Put the start and end dates in cells and join each comparison operator to its cell reference. This makes the report easier to update than changing dates inside the formula and reduces the risk of typing a date in an ambiguous format.

Does SUMIFS include both boundary dates?
Use a greater-than-or-equal-to start and an exclusive upper bound equal to the day after the final date. That pattern includes both calendar boundary dates, including records with times on the final date.

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.