To sum values by month in Excel, use SUMIFS with a helper column that extracts the month, or use SUMPRODUCT with the MONTH function directly. For example, if dates are in A2:A100 and amounts in B2:B100, enter =SUMPRODUCT((MONTH(A2:A100)=1)*B2:B100) to sum January amounts. This article explains both methods, how to handle years, and common pitfalls.
Why SUMIF Alone Can’t Handle Months
The standard SUMIF function compares a range to a single criterion, like a specific date or text. It cannot directly evaluate a condition like “month equals January” because it doesn’t perform calculations on the range. However, SUMIFS (the plural version) allows multiple criteria, but still requires a helper column if you want to use a month number. The SUMPRODUCT function, on the other hand, can perform array calculations and is the simplest way to sum by month without extra columns.
Method 1: SUMPRODUCT with MONTH Function
This method works in all Excel versions and doesn’t require a helper column. The MONTH function returns a number from 1 to 12 for a date. By comparing that to a month number, you get TRUE/FALSE, and multiplying by the amounts gives the sum for that month.
Example: Dates in A2:A100, amounts in B2:B100. To sum all amounts where the date falls in March (month 3):
=SUMPRODUCT((MONTH(A2:A100)=3)*B2:B100)
This returns the total of all March amounts. If you want to include a specific year, add a condition for the year as well:
=SUMPRODUCT((MONTH(A2:A100)=3)*(YEAR(A2:A100)=2024)*B2:B100)
Note: SUMPRODUCT treats TRUE as 1 and FALSE as 0, so the multiplication works. This formula is an array formula, but you don’t need to press Ctrl+Shift+Enter because SUMPRODUCT handles arrays natively.
Method 2: Helper Column with SUMIFS
If you prefer using SUMIFS (which is faster on large datasets), create a helper column that extracts the month number. For example, in column C, enter =MONTH(A2) and copy down. Then use:
=SUMIFS(B2:B100, C2:C100, 3)
This sums amounts where the helper column equals 3 (March). To include a year, add another helper column for year, or use a combined date like =DATE(YEAR(A2),MONTH(A2),1) to get the first of the month, then use SUMIFS with a date range.
Using Pivot Tables for Monthly Sums
For quick analysis, a Pivot Table is often the easiest way to sum by month. Select your data, go to Insert > PivotTable, drag the date field to Rows and the amount field to Values. Excel automatically groups dates by month, year, and quarter. If your dates are not grouping, right-click the date field and select Group, then choose Months and Years. This method is dynamic and doesn’t require formulas.
Common Problems and Fixes
- Dates stored as text: If your dates are text,
MONTHwill return a #VALUE! error. Convert text to dates usingDATEVALUEor the Text to Columns wizard (Data > Text to Columns > Delimited > Next > Date). - Blank cells in the date range:
MONTHof a blank cell returns 1 (January), which can skew results. UseIFto exclude blanks:=SUMPRODUCT((A2:A100<>"")*(MONTH(A2:A100)=3)*B2:B100). - Month names instead of numbers: If you have text month names, use
MONTH(DATEVALUE("1"&A2&"2024"))to convert, or use a lookup table. - SUMIFS with month name: You can use a text criterion like “March” if you have a helper column with text months, but that’s less flexible.
Comparing SUMPRODUCT vs SUMIFS Performance
For small datasets (under a few thousand rows), both methods work fine. For large datasets (100,000+ rows), SUMIFS with a helper column is generally faster because it doesn’t perform array calculations on every row. SUMPRODUCT calculates the entire array each time, which can slow down the workbook. If speed is a concern, use the helper column approach.
Alternatives: SUMIFS with Date Ranges
You can also sum by month using SUMIFS with two date criteria: the first day of the month and the last day. For example, to sum March 2024:
=SUMIFS(B2:B100, A2:A100, ">=3/1/2024", A2:A100, "<=3/31/2024")
This works without a helper column, but you must hard-code the dates or reference cells containing them. To make it dynamic, use EOMONTH to get the last day:
=SUMIFS(B2:B100, A2:A100, ">="&DATE(2024,3,1), A2:A100, "<="&EOMONTH(DATE(2024,3,1),0))
This is a good middle ground: no helper column, and it uses the efficient SUMIFS.
Frequently Asked Questions
How do I sum by month in Excel without a helper column?
Use SUMPRODUCT with the MONTH function as shown above. For example, =SUMPRODUCT((MONTH(A2:A100)=1)*B2:B100) sums January. This works without adding any extra columns.
Can I use SUMIF with a month name like "January"?
Yes, if your dates are text and you have a column with month names, you can use =SUMIF(C2:C100,"January",B2:B100) where C contains month names. But for real dates, you need to extract the month first.
Why does my SUMIFS return 0 when I use a month number?
Because SUMIFS compares exact values. If your dates are actual dates, comparing them to a number like 3 won't match. You need to use a helper column with MONTH, or use the date range method.
How do I sum by month and year together?
Add a year condition to SUMPRODUCT: =SUMPRODUCT((MONTH(A2:A100)=3)*(YEAR(A2:A100)=2024)*B2:B100). Or use SUMIFS with two helper columns for month and year.
Is there a way to sum by month using a Pivot Table?
Yes, Pivot Tables are the easiest. Drag dates to Rows, amounts to Values, then right-click the date and select Group > Months. This automatically sums by month without formulas.
What if my dates include times? Will MONTH still work?
Yes, MONTH extracts the month from a date-time value. The time portion is ignored. So MONTH("3/15/2024 14:30") returns 3.
Related
- Excel RV Window CH1006 Repair: Step-by-Step Guide
- Excel Sales Tracking Template by Customer: How to Build One
- Excel Sales Tracking Template by Product: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Pick Randomly From a ListWith a contiguous list in A2:A10, use =INDEX(A2:A10,RANDBETWEEN(1,ROWS(A2:A10))) to return one randomly selected item. The result can change…
- IFERROR in Excel: How to Catch and Handle Formula ErrorsTo use IFERROR in Excel, wrap a calculation or formula in IFERROR and choose what to show if…
- Excel Calculation for Hours Worked: Formulas and TipsFor a shift with start time in A2, end time in B2, and unpaid break duration in C2,…
- Add Days to Date in Excel vs. EDATEIf A2 contains a date and B2 contains a number of calendar days, add days in Excel with…
- Divide a Cell by a Number in Excel: Formula GuideTo divide a cell by a number in Excel, enter a formula such as =A2/5 in an empty…
- Excel Function to Lowercase? Use LOWERThe Excel function to convert text to lowercase is LOWER. For example, =LOWER("HELLO") returns hello, and you can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.