To sum values where the date falls in a specific month, use SUMIFS with two criteria that bracket the month: =SUMIFS(sum_range, date_range, ">="&DATE(year, month, 1), date_range, "<"&DATE(year, month+1, 1)). For example, to sum sales in January 2025 with dates in A2:A100 and amounts in B2:B100, use =SUMIFS(B2:B100, A2:A100, ">="&DATE(2025,1,1), A2:A100, "<"&DATE(2025,2,1)). This works in Excel for Microsoft 365, Excel 2021, and earlier versions.
Why SUMIFS Needs Two Date Criteria
Excel stores dates as serial numbers, so a date like January 15, 2025 is just a number. The SUMIFS function compares those numbers using operators like >= (greater than or equal) and < (less than). A single criterion like "January 2025" doesn't exist as a value in the date cells, so you must define the month as a range: from the first day of the month up to (but not including) the first day of the next month. This ensures every date in January is included and no dates from February leak in.
Step-by-Step: Build the Month-Equals Formula
- Identify your ranges: Suppose your dates are in
A2:A100and the values to sum are inB2:B100. - Pick a year and month: For January 2025, use
year=2025andmonth=1. - Write the lower bound:
">="&DATE(2025,1,1)– this means “on or after January 1, 2025.” - Write the upper bound:
"<"&DATE(2025,2,1)– this means “before February 1, 2025.” - Combine into SUMIFS:
=SUMIFS(B2:B100, A2:A100, ">="&DATE(2025,1,1), A2:A100, "<"&DATE(2025,2,1))
If your data includes times (e.g., 1/15/2025 3:00 PM), the < (less than) upper bound still works because February 1 at 00:00 is greater than any time on January 31. Using <= with the last day of the month would miss times on that last day, so the “less than first of next month” approach is safer.
Using a Cell Reference for the Month
Instead of typing the year and month into the formula, you can put a date in a cell (say D1) and use DATE(YEAR(D1), MONTH(D1), 1) for the lower bound and DATE(YEAR(D1), MONTH(D1)+1, 1) for the upper bound. This lets you change the month by editing one cell. The formula becomes:
=SUMIFS(B2:B100, A2:A100, ">="&DATE(YEAR(D1), MONTH(D1), 1), A2:A100, "<"&DATE(YEAR(D1), MONTH(D1)+1, 1))
If D1 contains 1/15/2025, the formula sums all dates in January 2025. Note: MONTH(D1)+1 works even for December because DATE(2025, 13, 1) automatically becomes January 1, 2026.
Alternatives: SUMIFS with EOMONTH and SUMPRODUCT
You can also use EOMONTH to define the month end. For a date in D1, the formula =SUMIFS(B2:B100, A2:A100, ">="&EOMONTH(D1,-1)+1, A2:A100, "<"&EOMONTH(D1,0)+1) works. Here EOMONTH(D1,-1)+1 gives the first day of the month, and EOMONTH(D1,0)+1 gives the first day of the next month. This is equivalent to the DATE version.
If you prefer a single formula without helper cells, SUMPRODUCT can handle it: =SUMPRODUCT((A2:A100>=DATE(2025,1,1))*(A2:A100
Common Problems and How to Fix Them
- Dates are text, not real dates: If your “dates” are left-aligned in cells, they may be text. SUMIFS won’t match them. Select the column, use Data > Text to Columns > Delimited > Finish, or use
DATEVALUEto convert. - Blank cells in the date range: Blank cells are ignored by SUMIFS; they won’t cause errors, but if you have blanks, the formula still works.
- Wrong result because of time components: If you used
<=with the last day of the month, dates with times on that last day are excluded. Use the “less than first of next month” method to avoid this. - #VALUE! error: This usually means a range has text that looks like a number, or the criteria are not concatenated properly. Check that you used
&and quotes correctly. - Month name in text: You cannot use
"January"as a criterion because Excel doesn’t compare text to serial dates. Stick to date numbers.
Comparing SUMIFS with PivotTables for Monthly Totals
A PivotTable can group dates by month with a few clicks: select your data, go to Insert > PivotTable, drag the date field to Rows, right-click a date and choose Group > Months. This gives you monthly totals without writing formulas. However, a PivotTable requires manual refresh when data changes, and it’s less flexible if you need the result in a specific cell for further calculations. SUMIFS updates automatically and can be nested inside other formulas. For a one-off report, a PivotTable is faster; for a dynamic dashboard, SUMIFS is better.
Frequently Asked Questions
Can SUMIFS compare to a month name like “January”?
No. SUMIFS compares values, not text labels. You must use date serial numbers. If you have a text month name in a separate column, you could use SUMPRODUCT with TEXT(date,"mmmm") but that is volatile and slower. Better to keep dates as real dates.
How do I sum by month and year, not just month?
The formula above already includes the year because it uses DATE(2025,1,1). If you only want to sum all Januarys across multiple years, you would need a different approach, like SUMPRODUCT((MONTH(A2:A100)=1)*B2:B100), but that ignores the year. Most users want a specific month in a specific year, so the two-criteria SUMIFS is correct.
What if my dates are in a different column than the values?
That’s fine. SUMIFS allows any ranges as long as they are the same size. For example, dates in C2:C100 and values in D2:D100 works the same.
Does this work in Google Sheets?
Yes, the same SUMIFS syntax works in Google Sheets. The only difference is that Google Sheets may auto-convert dates, but the formula is identical. In Excel for Mac, the formula is also the same; just use the same comma separators.
Why does my formula return 0 when I know there are matches?
Check that your date cells contain real dates (not text) and that the criteria are written correctly. Also, ensure the sum range is numeric. A common mistake is using "<="&DATE(2025,1,31) which misses times on Jan 31; use "<"&DATE(2025,2,1) instead.
Can I use a named range for the date range?
Yes, you can name a range (e.g., Dates) and use it in the formula: =SUMIFS(B2:B100, Dates, ">="&DATE(2025,1,1), Dates, "<"&DATE(2025,2,1)). This makes the formula easier to read.
Related
- Excel Power Query: Append to Existing Table
- Excel Power Query Background Refresh Explained
- Excel Power Query: Get Data From Folder
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Sum Highlighted Cells: Quick Methods and FormulasTo add numbers in cells you have selected, select them and read Sum on Excel’s status bar; to…
- Excel Sum of Filtered Data: SUBTOTAL, AGGREGATE & FILTERTo sum only the rows currently visible after filtering in Excel, put =SUBTOTAL(9,C2:C7) in a cell outside the…
- Extract a Word From a Cell in Excel With a FormulaTo return a particular word from a cell, put the text in A2 and the word’s position in…
- Outlook Folder Rules: Create Rules to Sort EmailCreate an Outlook rule by choosing a condition, such as a sender or subject phrase, and an action…
- Excel Formula for Removing Spaces: TRIM and MoreIn Excel, use =TRIM(A2) to remove leading and trailing spaces and reduce repeated spaces between words to one.…
- Excel Table Filters: Can You Filter Only Some Columns?Excel tables do not have a built-in setting to show filter arrows on only selected columns: the table’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.