To sum values for dates in Excel, use SUMIF for one date condition or SUMIFS for a start-and-end date range. With dates in A2:A7 and amounts in B2:B7, for example, the January 2025 total is =SUMIFS(B2:B7,A2:A7,">="&DATE(2025,1,1),A2:A7,"<"&DATE(2025,2,1)), which returns 230 when the January amounts are 120, 80, and 30.
How SUMIF works with Excel dates
SUMIF adds numbers from a sum range when the corresponding cells in a criteria range meet one condition. Dates can be used as that condition because Excel stores genuine dates as serial numbers. The display may look like a calendar date, but Excel compares the underlying numeric value.
The function has three arguments: the range to check, the criterion, and the range to add. The ranges should normally have the same shape and cover matching rows. If you leave out the sum range, Excel adds the cells in the criteria range itself; that is usually not what you want when dates are in one column and amounts in another.
For a date condition, the criterion can be a specific date or a comparison such as on or after a date. When a criterion uses an operator such as greater than or equal to, join the operator text to a date value with the ampersand. The DATE function is a reliable way to construct a date in a formula because its year, month, and day arguments do not depend on how a date is written in the computer's regional format.
Sum values for one exact date
Suppose A2:A7 contains transaction dates and B2:B7 contains amounts. The rows are Jan. 1, Jan. 15, Feb. 1, Feb. 28, Mar. 1, and Jan. 31, 2025; their corresponding amounts are 120, 80, 200, 50, 90, and 30. To add only transactions dated Jan. 15, use:
With dates in A2:A7 and amounts in B2:B7, this exact-date formula returns 80:
=SUMIF(A2:A7,DATE(2025,1,15),B2:B7)
A date typed directly into a criterion can be interpreted differently on computers with different regional settings. Using DATE(2025,1,15) avoids ambiguous text such as 1/2/2025, which may mean January 2 or February 1 depending on the system. You can also put a date in a worksheet cell and refer to that cell as the criterion, which is useful when the reporting date changes often.
Sum a date range with SUMIFS
A common request is to add every amount from a start date through an end date. SUMIF accepts only one criterion, so it cannot directly express both boundaries. Use SUMIFS, which supports multiple criteria, and apply both to the date column: one condition for the first day and another for the day after the final date.
With dates in A2:A7 and amounts in B2:B7, the following includes Jan. 1 through Jan. 31, 2025, and returns 230:
=SUMIFS(B2:B7,A2:A7,">="&DATE(2025,1,1),A2:A7,"<"&DATE(2025,2,1))
The second boundary is less than Feb. 1 rather than less than or equal to Jan. 31. This is a useful pattern because it also handles dates that include times, such as Jan. 31 at 3:00 p.m. An inclusive upper limit of Jan. 31 at midnight could miss those later timestamps. For a whole-day range, use the first date with a greater-than-or-equal condition and the day after the last date with a less-than condition.
There is a practical benefit to this approach beyond timestamps: it avoids calculating the last possible time of a day. Excel time values can include fractions of a day, so an upper bound such as 11:59:59 p.m. may not include every possible stored time. The next-day boundary is simpler and dependable.
Use worksheet cells for changing date criteria
For a reusable report, place the first date in D1 and the last date in E1. Format those cells as dates and enter new reporting dates there when needed. With transaction dates in A2:A7, amounts in B2:B7, and D1 set to Jan. 1, 2025, and E1 set to Jan. 31, 2025, this formula returns 230:
=SUMIFS(B2:B7,A2:A7,">="&D1,A2:A7,"<"&E1+1)
Adding 1 to E1 makes the upper boundary the start of the following day. This version is handy when the end date is entered by a person rather than generated by a formula. Check that D1 and E1 contain actual Excel dates, not text that merely looks like a date. If the report should use an exclusive end date already supplied in a cell, use that cell directly as the less-than boundary instead of adding 1.
Keep the criteria range and sum range aligned. For example, if the dates begin in row 2, the amounts should also begin in row 2. Including a header in one range but not the other can shift the matches and produce a result that looks plausible but is wrong.
Get totals by month or year
For a monthly total, set the lower limit to the first day of the month and the upper limit to the first day of the next month. This works for months with 28, 29, 30, or 31 days without needing to find the month's last date manually. With dates in A2:A7 and amounts in B2:B7, this formula totals January 2025 and returns 230:
=SUMIFS(B2:B7,A2:A7,">="&DATE(2025,1,1),A2:A7,"<"&DATE(2025,2,1))
For a year total, use the first day of that year and the first day of the next year. With the same six-row example, all dates are in 2025 and the amounts total 570:
=SUMIFS(B2:B7,A2:A7,">="&DATE(2025,1,1),A2:A7,"<"&DATE(2026,1,1))
To make the year dynamic, you can store a year such as 2025 in a cell and construct the two boundaries with DATE. This is preferable to matching the displayed year text, and it works even when the cells display dates in a different format. In a pivot report or dashboard, keep the reporting year in a clearly labeled input cell so users can see which period the total represents.
Dates that include times
Excel dates can include a time component. For instance, a cell can hold Jan. 1, 2025 at 2:30 p.m. while its number format displays only Jan. 1, 2025. A criterion for exact equality with midnight will not match that value. This explains why an exact-date SUMIF may appear to omit transactions even though the displayed dates look identical.
For a full calendar day, use two boundaries instead of equality. With timestamps in A2:A4 and amounts in B2:B4, suppose the entries are Jan. 1 at 9:00 a.m. for 40, Jan. 1 at 2:30 p.m. for 60, and Jan. 2 at 10:00 a.m. for 25. The formula below returns 100 for Jan. 1:
=SUMIFS(B2:B4,A2:A4,">="&DATE(2025,1,1),A2:A4,"<"&DATE(2025,1,2))
Changing the date display to show a time can help diagnose this issue, but it does not change the stored values. The boundary method is usually safer than removing times from the source data, particularly when the time information is needed for other reports.
Common problems and how to fix them
- Dates are stored as text. A cell imported from a CSV or copied from a website may look like a date but be text. Date criteria may then fail to match. Check a few source cells, convert them to real dates if needed, and confirm the results before relying on the total.
- The criterion is assembled incorrectly. For a comparison, put the operator in quotation marks and join it to the date value. The ampersand connects the text operator to the date serial value. Omitting it or placing the date inside an unsuitable text string can make the condition fail.
- An end date is excluded. A criterion for dates less than the end date excludes that date. To include a chosen end date, set the upper boundary to the following day and use a less-than condition, or use a less-than-or-equal condition only when timestamps are not a concern.
- Ranges do not match. The date range and sum range should contain corresponding rows. If one range is A2:A100 and the other starts at B3, the formula is not evaluating the intended pairs.
- Regional date formats cause ambiguity. A typed date such as 03/04/2025 can be read differently in different locales. Build criteria with
DATEor refer to a cell containing a verified date. - There are hidden time values. A cell may display only the date while containing a time. Use a range of dates with lower and exclusive upper bounds to include every time on the intended day.
If the answer still looks wrong, verify a small sample manually: inspect the underlying dates, check the matching rows, and add their amounts. Also confirm that the cells being summed are numeric. Text-formatted amounts may not be included as expected, even if they look like numbers.
SUMIF or SUMIFS: which should you use?
Use SUMIF when one condition is enough, such as one exact date or all dates on or after a specified day. Use SUMIFS when you need a bounded date range or another condition at the same time, such as dates in January and a particular department. In SUMIFS, the sum range comes first, followed by each criteria range and criterion pair; this order differs from SUMIF.
For a simple one-date total, SUMIF is compact and readable. For most date-period reports, SUMIFS is easier to maintain because the start and end conditions are visible and can be extended with additional criteria. You do not need to replace a working SUMIF simply because the criterion is a date; choose based on how many conditions the calculation needs.
Frequently Asked Questions
Can SUMIF sum between two dates? Not with two independent conditions in a single SUMIF call. Use SUMIFS with a start-date condition and an end-date condition, usually with an exclusive boundary on the day after the final date.
Why does SUMIF return zero for dates that look the same? The source dates may be stored as text, or they may include times that are not visible in the current number format. Confirm that the cells contain real dates and, for a whole day, use a lower and upper date boundary.
Can I use a date in another cell as the criterion? Yes. Refer to the cell directly for a single-date condition. For a range, join the comparison operators to the start and end date cells in a SUMIFS formula. Ensure the referenced cells contain actual date values.
How do I sum every date in a month? Set the lower boundary to the month's first day and the upper boundary to the first day of the next month. Use greater than or equal to the first boundary and less than the second. That pattern works for every month length and includes timestamps within the month.
Should I use SUMIF or SUMIFS for dates? Use SUMIF for one condition, such as one exact date. Choose SUMIFS when you need both a start and end date or additional filters, such as a category or salesperson.
Related
- Compare Two Excel Spreadsheets Quickly: Best Methods
- Shade Every Other Row in Excel: Quick, Reliable Methods
- Quick Way to Shade Every Other Row in Excel Automatically
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Group Dates by Month in a Filter: Steps & FixesTo group dates by month in an Excel filter, make sure the column contains real Excel dates, then…
- CONCAT in Excel: Add a Space Between Text ValuesTo add a space when joining text in Excel, include a space in quotation marks as an argument:…
- Excel Time Difference in Minutes: Formulas and TipsTo calculate the minutes between a start time in A2 and an end time in B2, enter =(B2-A2)*1440…
- Excel INDEX Errors: Common Causes, Meanings, and FixesAn Excel INDEX error usually means the requested row or column falls outside the range (#REF!), an index…
- XLOOKUP Spreadsheet Examples for Everyday Excel TasksXLOOKUP finds a value in one range and returns the corresponding value from another range; for example, with…
- Excel: How to Combine Names in Two Columns with a FormulaTo combine names in two Excel columns, enter =A2&" "&B2 in a blank cell on row 2, replacing…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.