Excel SUMIF by Date: Formula & Examples

Formulas & functions · Updated November 22, 2026

To sum values in Excel based on a date, use the SUMIF function with the date as the criteria, like =SUMIF(A2:A100, "2025-01-15", B2:B100) where column A holds dates and column B holds the values to sum. For a range of dates (e.g., all of January), use SUMIFS with two criteria, one for the start date and one for the end date. This article explains both formulas, how to handle date formatting, and common pitfalls.

Why Date Criteria in SUMIF Can Be Tricky

Excel stores dates as serial numbers—each day is a whole number counting from January 1, 1900. For example, January 1, 2025 is serial number 45658. When you type a date in a cell, Excel displays it as a date, but the underlying value is that serial number. This matters because SUMIF compares the criteria you give it against these serial numbers, not the text you see. If you write "1/15/2025" as text in the formula, Excel usually converts it to a date automatically, but if your system’s date format is different (e.g., day/month vs. month/day), the formula can misinterpret the date or return zero. The safest approach is to reference a cell containing a real date, or use the DATE function inside the formula.

The Basic SUMIF Formula with a Single Date

Assume you have dates in A2:A20 and amounts in B2:B20. To sum all amounts for January 15, 2025, enter this formula in any empty cell:

=SUMIF(A2:A20, DATE(2025,1,15), B2:B20)

The DATE function returns the correct serial number, avoiding text-format issues. If you prefer to reference a cell, put the date in D1 and use =SUMIF(A2:A20, D1, B2:B20). The result will be the total of all B values where the corresponding A value equals that exact date. Note that SUMIF matches the entire date, including time if any—if your dates have times (e.g., 1/15/2025 3:00 PM), the match will fail unless the time is also identical.

Using SUMIFS for a Date Range (e.g., a Month)

To sum values between two dates—say, all of January 2025—use SUMIFS because it supports multiple criteria. The syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). For dates in A2:A20 and amounts in B2:B20, sum amounts where the date is on or after January 1 and on or before January 31:

=SUMIFS(B2:B20, A2:A20, ">="&DATE(2025,1,1), A2:A20, "<="&DATE(2025,1,31))

The ampersand (&) joins the comparison operator (like ">=" ) with the date serial number. This formula returns the total for the entire month. For a dynamic month, put the first day of the month in E1 (e.g., 1/1/2025) and use =SUMIFS(B2:B20, A2:A20, ">="&E1, A2:A20, "<"&EDATE(E1,1)). The EDATE function adds one month, giving you the first day of the next month, so the second criteria is strictly less than that—capturing all dates in the month.

How to Handle Dates Stored as Text

A very common problem is that your date column contains text that looks like a date (e.g., "01/15/2025" as a string, not a real date). SUMIF will not match text against a real date serial number, returning 0. To check, select the date column and look at the Number Format dropdown—if it shows "Text" or the cell alignment is left, it’s likely text. To fix, select the column, go to Data > Text to Columns, click Next twice, then choose Date and select MDY, then Finish. Alternatively, use DATEVALUE in a helper column: put =DATEVALUE(A2) in C2 and drag down—this converts text dates to real dates, then use that column in your SUMIF. If the text is in a non-standard format like "Jan 15, 2025", DATEVALUE usually handles it, but if it fails, you may need to clean the text first.

Common Problems and How to Fix Them

  • Formula returns 0: Check that the dates are real dates (not text), that the criteria cell contains a date, and that the ranges are the same size. Also ensure there are no hidden spaces in the criteria.
  • Wrong total because of time components: If your dates include times, use SUMIFS with criteria like ">="&DATE(2025,1,15) and "<"&DATE(2025,1,16) to capture the entire day.
  • Regional date format confusion: If you type "1/2/2025" in a formula, Excel interprets it based on your system’s locale—January 2 in the US, February 1 in the UK. Always use DATE(2025,1,2) to be unambiguous.
  • SUMIF vs SUMIFS argument order: SUMIF puts the sum range last; SUMIFS puts it first. Mixing them up is a frequent error.

Comparing SUMIF, SUMIFS, and SUMPRODUCT for Dates

SUMIF is best for a single exact date match. SUMIFS is the go-to for date ranges and multiple conditions (e.g., date range plus a product name). SUMPRODUCT is more flexible but slower on large datasets; it can handle array calculations without needing Ctrl+Shift+Enter. For example, to sum amounts where the date is in January and the product is "Widget", you could use =SUMPRODUCT((A2:A20>=DATE(2025,1,1))*(A2:A20<=DATE(2025,1,31))*(C2:C20="Widget")*B2:B20). However, SUMIFS is more readable and faster: =SUMIFS(B2:B20, A2:A20, ">="&DATE(2025,1,1), A2:A20, "<="&DATE(2025,1,31), C2:C20, "Widget"). In most cases, prefer SUMIFS unless you need to perform calculations on the criteria (like extracting the month from a date) that SUMIFS cannot do directly.

Using Dynamic Dates with TODAY and EOMONTH

To sum values for the current month automatically, use TODAY() to get today’s date and EOMONTH to find the end of the month. For dates in A2:A20 and amounts in B2:B20, the formula for the current month is:

=SUMIFS(B2:B20, A2:A20, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A2:A20, "<"&EOMONTH(TODAY(),0)+1)

This calculates the first day of the current month and the first day of the next month (by adding 1 to the last day of the current month). It updates every time the workbook recalculates. For the last 30 days, use =SUMIFS(B2:B20, A2:A20, ">="&TODAY()-30, A2:A20, "<="&TODAY()). These are handy for dashboards and reports.

Frequently Asked Questions

Why does my SUMIF with a date return 0 even though the date exists?
Most likely the date in the criteria is text or the date in the data range includes a time component. Use DATE() in the formula, and if times are present, use SUMIFS with ">=" and "<" for the next day.

Can I use a cell reference with a date in SUMIF?
Yes, absolutely. Put a real date in a cell (e.g., D1) and use =SUMIF(A2:A20, D1, B2:B20). This is often safer than typing the date in the formula because it avoids text conversion issues.

How do I sum by month without a helper column?
Use SUMPRODUCT with MONTH() and YEAR() functions: =SUMPRODUCT((MONTH(A2:A20)=1)*(YEAR(A2:A20)=2025)*B2:B20). This works but is slower than SUMIFS on large ranges.

Does SUMIF work with dates in a PivotTable?
No, PivotTables have their own calculation engine. To sum by date in a PivotTable, add the date field to the Rows area, then group by month or day, and the Values area will sum automatically.

What if my dates are in a different column than the values?
That’s fine—the criteria range and sum range can be different columns, as long as they have the same number of rows. For example, dates in A, values in D: =SUMIF(A2:A20, DATE(2025,1,15), D2:D20).

Is there a way to sum based on a date in the cell's format, like "January"?
Not directly with SUMIF because it compares values, not formatting. You’d need to use SUMIFS with a date range, or add a helper column with =TEXT(A2,"mmmm") to extract the month name, then SUMIF on that helper column.

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.