Yes, you can sum values in Excel by month using the SUMIFS function with two criteria: one for the first day of the month and one for the last day. For example, with dates in A2:A100 and amounts in B2:B100, the formula =SUMIFS(B2:B100,A2:A100,">="&DATE(2025,1,1),A2:A100,"<="&DATE(2025,1,31)) sums all amounts with dates in January 2025. This works because Excel stores dates as serial numbers, so comparisons like ">=" and "<=" operate on numeric values.
Why SUMIF Alone Isn't Enough
The classic SUMIF function accepts only one condition, so you can't directly tell it "sum if the month is January." You could try =SUMIF(A:A,"January",B:B), but that would look for the text string "January" in the date cells, which doesn't exist. Dates are numbers, not text. The workaround is either to add a helper column that extracts the month number or name, or to use SUMIFS with date range criteria. The latter is cleaner because it avoids altering your data.
Using SUMIFS with Date Range Criteria
The SUMIFS function sums cells that meet multiple criteria. For a month, you need two conditions: the date is on or after the first day of the month, and on or before the last day. Here's the layout: dates in A2:A20, amounts in B2:B20. To sum all amounts in March 2025, use:
=SUMIFS(B2:B20, A2:A20, ">="&DATE(2025,3,1), A2:A20, "<="&DATE(2025,3,31))
This returns the total of all B values where the corresponding A date is between March 1 and March 31, 2025. The DATE function builds a real date, and the ampersand (&) concatenates the comparison operator with that date. You can replace the hard-coded year and month with cell references, e.g., C1 containing a date like 3/1/2025, then use =SUMIFS(B2:B20, A2:A20, ">="&DATE(YEAR(C1),MONTH(C1),1), A2:A20, "<="&EOMONTH(C1,0)).
Using EOMONTH for the Last Day
Instead of manually typing the last day (which varies by month), use EOMONTH to get the last day of the month. EOMONTH(start_date, months) returns the last day of the month that is a specified number of months from the start date. For example, =EOMONTH(DATE(2025,3,1),0) returns 3/31/2025. So a more robust formula for March 2025 is:
=SUMIFS(B2:B20, A2:A20, ">="&DATE(2025,3,1), A2:A20, "<="&EOMONTH(DATE(2025,3,1),0))
This works for any month, including February with 28 or 29 days. If you have a date in cell C1, use =SUMIFS(B2:B20, A2:A20, ">="&DATE(YEAR(C1),MONTH(C1),1), A2:A20, "<="&EOMONTH(C1,0)). This is the most flexible approach.
Alternative: Helper Column with MONTH()
Some users prefer a helper column because it makes the logic visible and allows using the simpler SUMIF. In column C, enter =MONTH(A2) for each row. This returns a number from 1 to 12. Then to sum amounts for month 3 (March), use =SUMIF(C2:C20, 3, B2:B20). You can also use TEXT(A2,"mmm") to get "Mar" and then sum by that text. However, helper columns add clutter and require updating if you add rows. The SUMIFS method is more efficient and keeps your data clean.
Summing by Month from a Single Date Cell
If you have a single cell containing a date (say, D1 = 3/15/2025) and you want to sum all amounts for that month, use the EOMONTH approach with cell references:
=SUMIFS(B2:B20, A2:A20, ">="&DATE(YEAR(D1),MONTH(D1),1), A2:A20, "<="&EOMONTH(D1,0))
This dynamically adjusts to whatever month is in D1. For example, if D1 is 3/15/2025, it sums March 1–31. If D1 is 12/1/2025, it sums December 1–31. This is ideal for dashboards where you change a single date to update the monthly total.
Common Problems and Fixes
- Dates stored as text: If your dates are text like "01/15/2025",
SUMIFSwon't match them. Convert them to real dates usingDATEVALUEor the Text to Columns feature (Data > Text to Columns > Delimited > Next > Next > Date > MDY). - Blank or zero dates: If column A has blanks, they are treated as 0 (January 0, 1900), which might accidentally match if your criteria include that. Use
=SUMIFS(B2:B20, A2:A20, ">="&DATE(...), A2:A20, "<="&EOMONTH(...), A2:A20, "<>")to exclude blanks. - Whole column references: Using
A:Aworks but can slow down calculations. Limit to the actual data range. - Locale differences: In some Excel versions, the argument separator is a semicolon (;) instead of a comma. Adjust accordingly.
Frequently Asked Questions
Can I use SUMIF with a month name like "January"?
Not directly on a date column. If you have a helper column with =TEXT(A2,"mmmm") to produce "January", then =SUMIF(C:C,"January",B:B) works. But without a helper, you must use SUMIFS with date criteria.
How do I sum by month and year, e.g., only March 2025?
Use the SUMIFS formula with the first and last day of that specific month, as shown above. The DATE(YEAR, MONTH, 1) and EOMONTH combo ensures both month and year are correct.
What if I want to sum for the current month automatically?
Use =SUMIFS(B2:B20, A2:A20, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A2:A20, "<="&EOMONTH(TODAY(),0)). This updates every time the workbook recalculates.
Does this work in Google Sheets?
Yes, the same SUMIFS and EOMONTH functions exist in Google Sheets. The syntax is identical, though the separator may be a semicolon depending on your locale.
Can I sum by month using a PivotTable instead?
Yes. Create a PivotTable, drag the date field to Rows, right-click and choose "Group", then select "Months". Drag the amount field to Values. This gives a monthly total without formulas, but it's static unless refreshed.
Comparison: SUMIFS vs. SUMPRODUCT
An alternative is SUMPRODUCT with month extraction: =SUMPRODUCT((MONTH(A2:A20)=3)*(B2:B20)). This sums amounts where the month number is 3. It works but is less efficient for large datasets because it processes arrays. SUMIFS is faster and easier to read. If you need to sum by month across multiple years, SUMPRODUCT can handle (YEAR(A2:A20)=2025)*(MONTH(A2:A20)=3), but you can also add a year criterion to SUMIFS with an additional range. Stick with SUMIFS for clarity and performance.
Related
- Excel Sales Tracking Template by Product: How to Build One
- Excel Reverse Mortgage Calculator Spreadsheet
- Excel Reverse Lookup with Multiple Criteria: How-To
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel Partial Text: Match Words with WildcardsYes. To count cells that contain partial text in Excel, use COUNTIF with asterisks as wildcards. With the…
- Excel Count If Not Blank: Formulas and Common TrapsTo count cells that are not blank in Excel, use =COUNTA(A2:A100), replacing the range with your data. If…
- Excel Days Until End of Month: Formula and ExamplesWith no input cells needed, enter =EOMONTH(TODAY(),0)-TODAY() to calculate the number of days from today to the last…
- Excel LAMBDA Formula Examples for Reusable FunctionsExcel LAMBDA lets you turn a calculation into a reusable function, so you can name it once and…
- Oracle String Aggregation Functions ExplainedOracle’s main string aggregation function is LISTAGG: it combines values from multiple rows into one delimited string, with…
- Filter Search Shortcut in Excel: Find Values FastIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn column filters on or off. To search…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.