How to Use SUMIF with Dates in Excel (and Google Sheets) – Step‑by‑Step Guide
1. SUMIF Basics
SUMIF adds the values in sum_range that meet a single condition applied to range.
Syntax
=SUMIF(range, criteria, [sum_range])
| Argument | Required? | Typical use with dates |
|---|---|---|
range |
Yes | Column that holds the dates you want to test |
criteria |
Yes | A comparison operator plus a date, e.g. ">=2024-02-01" or ">="&A1 |
sum_range |
No | Column of numbers to add; if omitted Excel sums range itself |
Quick example
| A (Date) | B (Sales) |
|---|---|
| 2024‑01‑05 | 120 |
| 2024‑01‑12 | 85 |
| 2024‑02‑03 | 200 |
| 2024‑02‑20 | 150 |
=SUMIF(A2:A5, ">=2024-02-01", B2:B5)
Result: 350 (sales on or after 1 Feb 2024).
2. Preparing Date Data
SUMIF works only with real date serial numbers, not text that merely looks like a date. Both Excel and Google Sheets store dates as numbers (e.g., 45292 = 2024‑01‑01).
Checklist
| ✔️ | Item |
|---|---|
| Format ≠ value | Changing a cell’s format to “Short Date” does not convert text. |
| Locale | 01/02/2023 can mean Jan 2 or Feb 1 depending on regional settings. |
| Spaces | Leading/trailing spaces keep the entry as text. |
Converting Text to Dates
| A (raw) | B (formula) | B (result) |
|---|---|---|
2024/01/15 |
=DATEVALUE(A2) |
45306 |
15‑Jan‑2024 |
=DATEVALUE(A3) |
45306 |
2024‑01‑15 |
=TRIM(A4) → =DATEVALUE(TRIM(A4)) |
45306 |
Use Data → Text to Columns → Finish or wrap the cell in DATEVALUE to coerce text to a true date.
3. SUMIF with an Exact Date
To sum values for a single day, supply that day as the criterion. You can type the date, reference a cell, or build it with DATE.
=SUMIF(A2:A6, DATE(2024,1,15), C2:C6)
| OrderDate | Region | Amount |
|---|---|---|
| 1/15/2024 | East | 500 |
| 1/15/2024 | West | 300 |
| 1/16/2024 | East | 450 |
| 1/17/2024 | North | 600 |
| 1/15/2024 | South | 250 |
Result: 1050 (all sales on 15 Jan 2024).
Tip: When the criterion is in a cell (e.g., C1), use ">="&C1 or "="&C1 to avoid locale‑related misinterpretation.
4. Summing by Month or Year
SUMIF cannot run a function such as TEXT or MONTH over its range argument, so use SUMIFS with a start and end date instead.
4.1 Sum by month (February 2024)
=SUMIFS(B2:B7, A2:A7, ">="&DATE(2024,2,1), A2:A7, "<"&DATE(2024,3,1))
With the sample data in the full example below, this returns 350 (200 + 150).
4.2 Sum by year (2024)
=SUMIFS(B2:B7, A2:A7, ">="&DATE(2024,1,1), A2:A7, "<"&DATE(2025,1,1))
4.3 Sum by month with SUMPRODUCT
=SUMPRODUCT((MONTH(A2:A7)=2)*(YEAR(A2:A7)=2024)*B2:B7)
This also returns 350 and works in both Excel and Google Sheets.
5. Handling Dates that Include Times
If a column contains timestamps (e.g., 2024‑03‑15 08:30), an exact-date criterion misses them because the time adds a fraction. Use a start and end boundary with SUMIFS:
=SUMIFS(B2:B10, A2:A10, ">="&DATE(2024,3,15), A2:A10, "<"&DATE(2024,3,16))
| A (date‑time) | B (sales) |
|---|---|
| 2024‑03‑15 08:30 | 120 |
| 2024‑03‑15 14:45 | 200 |
| 2024‑03‑16 09:00 | 150 |
Result: 320 (sales for 15 Mar 2024, regardless of time).
6. Common Errors & Quick Fixes
| Symptom | Likely Cause | One‑Line Fix |
|---|---|---|
#VALUE! |
Criteria is text that Excel can’t coerce to a date. | Convert the column with DATEVALUE or use the DATE function. |
| Wrong total (too high/low) | Mixed data types (numbers stored as text) or missing comparison operator. | Ensure sum_range holds numbers; use ">="&DATE(2023,1,1). |
| Returns 0 | Dates contain hidden time values or the criterion’s format doesn’t match the locale. | Use a start/end range with SUMIFS or use DATE(year,month,day) for the criterion. |
#NAME? |
Function not available in the current Excel version. | Upgrade to Excel 365/2021 or replace with older equivalents (e.g., SUMPRODUCT). |
#N/A in adjacent cells |
Criteria cell is blank or contains an error. | Guard with =IF(ISNUMBER(C1),SUMIF(...),0). |
Mini‑Tips
- Always store dates as numbers – use the date picker or
DATE(y,m,d). - Lock the criterion cell when copying formulas (
$C$1). - Use
SUMIFSwith start and end dates when cells hold times.
7. FAQs
Q: Can I use SUMIFS instead of SUMIF for dates?
A: Yes. SUMIFS lets you apply multiple criteria (e.g., “≥ 1 Jan 2024” and “< 2 Jan 2024”) and works the same way with dates.
Q: Does SUMIF accept a cell reference for the criteria?
A: Absolutely. Write the date in a cell (e.g., C1) and reference it: =SUMIF(A2:A100, ">="&C1, B2:B100). Using & concatenates the operator with the cell’s value.
Q: How do I sum dates that are stored as text?
A: Convert the text to real dates first (=DATEVALUE(A2)) or use a helper column that applies DATEVALUE, then run SUMIF on that helper column.
Q: Are there any version limits I should know about?
A: SUMIF itself exists in all Excel versions. Functions used inside the criteria (e.g., TEXT, INT, MONTH) are also universally available. Newer functions like LET, LAMBDA, XLOOKUP, FILTER, and UNIQUE require Excel 365/2021; they are not needed for basic date‑based SUMIF work.
Q: What’s the Google Sheets equivalent of SUMIF with dates?
A: The same SUMIF and SUMIFS syntax works with DATE() boundaries.
Full Example (Excel & Google Sheets)
| A (Date) | B (Sales) |
|---|---|
| 2024‑01‑05 | 120 |
| 2024‑01‑12 | 85 |
| 2024‑02‑03 | 200 |
| 2024‑02‑20 | 150 |
| 2024‑03‑15 08:30 | 110 |
| 2024‑03‑15 14:45 | 190 |
Sum sales on or after 1 Feb 2024 (times ignored):
=SUMIFS(B2:B7, A2:A7, ">="&DATE(2024,2,1))
Result: 650 (200 + 150 + 110 + 190).