Sum by Month in Excel Columns: 3 Easy Ways

To sum values by month when your data has dates in one column and amounts in another, use the SUMIFS function with dates that fall within the month’s start and end, or use SUMPRODUCT with the MONTH function. You enter your date range, your amount range, and a month reference, and the formula returns the total for that month. This article explains the three most reliable methods, how to set them up, and how to avoid the common errors that trip people up.

Formula

The core idea is to test each date to see if its month matches your target month, then add the amounts where the test is true. In Excel terms, the SUMPRODUCT version looks like this:

SUMPRODUCT((MONTH(date_range)=month_number)*amount_range)

Where:

• date_range is the column of actual dates (e.g., A2:A100)
• month_number is the month you want, 1 for January through 12 for December
• amount_range is the column of values to add (e.g., B2:B100)

The MONTH function returns 1-12 for each date. The comparison (MONTH(…)=month_number) produces TRUE or FALSE, which Excel treats as 1 or 0 when multiplied. So the formula adds the amount only when the month matches.

The SUMIFS version uses two criteria to bracket the month:

SUMIFS(amount_range, date_range, “>=”&start_date, date_range, “<="&end_date)

Where start_date is the first day of the month and end_date is the last day. You can build these with DATE(year, month, 1) and EOMONTH(start_date, 0).

Worked Example

Using the calculator defaults above: month = 3 (March), date = 45000 (which is March 15, 2023 in Excel’s serial system), and amount = 250.

Step 1: Extract the month from the date: MONTH(45000) = 3.

Step 2: Compare to the target: 3 = 3, so TRUE (which equals 1).

Step 3: Multiply: 250 * 1 = 250.

The calculator shows 250.00, which is the amount itself because the single date falls in March. In a real sheet with many dates, the formula would add every amount whose date’s month equals 3.

Method 1: SUMPRODUCT with MONTH (simplest for a single year)

This is the easiest to write and read. It works perfectly when all your dates are in one year, because it only checks the month number, not the year.

Example: you have sales in A2:A50 and amounts in B2:B50. To total March:

=SUMPRODUCT((MONTH(A2:A50)=3)*B2:B50)

Drag the formula down and change the 3 to 4, 5, 6, etc. for other months. The downside: if your data spans multiple years, March 2022 and March 2023 will be added together. If that is what you want, this method is perfect. If not, use SUMIFS.

Method 2: SUMIFS with date brackets (handles multiple years)

SUMIFS compares full dates, so it can distinguish March 2023 from March 2024. You need a start and end date for the target month.

Put a real date in a cell, say D1 = 3/1/2023. Then:

=SUMIFS(B2:B50, A2:A50, “>=”&D1, A2:A50, “<="&EOMONTH(D1,0))

EOMONTH(D1,0) returns the last day of the month. This formula adds only amounts with dates between March 1 and March 31, 2023.

To make a full report, put the first of each month in a column (e.g., D1=1/1/2023, D2=2/1/2023, etc.) and copy the formula down. Each row gives that specific month’s total.

Method 3: PivotTable (fastest for a full report)

If you need totals for every month at once, a PivotTable does it without writing any formulas.

1. Select your data (headers included).
2. Insert > PivotTable > OK.
3. Drag the date field to Rows and the amount field to Values.
4. Right-click any date in the pivot, choose Group, select Months (and Years if you want to separate years), then OK.

The pivot automatically groups all dates into months. If your dates include times, uncheck the time grouping. This is the best choice when you want a quick summary and do not need the formula in a cell.

Common Mistakes and How to Avoid Them

• Dates stored as text: If your “dates” are left-aligned in the cell, they are probably text. MONTH() will return #VALUE!. Fix by selecting the column, Data > Text to Columns > Next > Next > choose Date (MDY) > Finish.
• Hidden rows or filtered data: SUMPRODUCT and SUMIFS ignore hidden rows only if you use SUBTOTAL. Regular SUMIFS includes hidden rows. If you filter and want the visible total, use SUBTOTAL(109, range) instead.
• Mixing years accidentally: As noted, SUMPRODUCT with MONTH ignores the year. If your data spans 2022 and 2023, January totals will combine both. Add a YEAR check: =SUMPRODUCT((MONTH(A2:A50)=1)*(YEAR(A2:A50)=2023)*B2:B50).
• Blank cells in the date column: MONTH(blank) returns 1 (January in Excel’s logic), so blanks get counted as January. Filter out blanks or use SUMIFS instead, which ignores blank dates.
• Using “MM” format in a cell that is not a date: Typing “03” as text does not work. Always use a real date or a number 1-12.

When to Use Which Method

• Single year, quick total: SUMPRODUCT with MONTH.
• Multiple years, need specific month-year totals: SUMIFS with EOMONTH.
• Full monthly report, no formulas wanted: PivotTable grouping.
• Data added daily, need auto-updating totals: SUMIFS referencing a cell that contains the first of the month; update that one cell each month.

If your data has more than a few thousand rows, SUMIFS is faster than SUMPRODUCT because it uses an index. SUMPRODUCT calculates every row, which can slow down large sheets.

Frequently Asked Questions

Why does my SUMPRODUCT return 0 even though I have data for that month?
Check that the dates are real dates, not text. Select a date cell and look at the formula bar: if it shows the date, it is a date; if it shows text, convert it. Also check that the month number you typed matches the actual month (January = 1, not 0).

How do I sum by month AND year together?
Use SUMIFS with two date brackets: start date = DATE(year, month, 1) and end date = EOMONTH(start, 0). Or add a YEAR check inside SUMPRODUCT as shown above.

Can I sum by month name (like “March”) instead of a number?
Yes, but you need to convert the name to a number. Use =MONTH(DATEVALUE(“1 “&A1& ” 2023″)) where A1 contains “March”. Then use that number in SUMPRODUCT or SUMIFS.

Does SUMIFS work with dates in a different column than the amounts?
Yes, that is the normal setup. The criteria range (dates) and sum range (amounts) can be different columns, as long as they are the same size.

What if my “month” is actually a text like “2023-03”?
That is not a real date, so MONTH() will fail. Convert it with =DATE(LEFT(A1,4), MID(A1,6,2), 1) to get the first of that month, then use SUMIFS.

Why does my PivotTable show dates grouped by quarter instead of month?
Right-click a date in the pivot, choose Group, then uncheck Quarters and check only Months (and Years if needed). Click OK and it will regroup.

Related

• pCloud Independence Day Offer: What to Know
• Open vs Closed Ecosystems: How to Choose
• Repeat Header and Footer in Word: Step-by-Step Guide

in month | Month number (1-12) | - | 3 | 1
in d | Date (as serial number) | - | 45000 | 0
in amt | Amount | - | 250 | 0
out sum_month | Sum for that month | - | amt * (month(d) = month) | 2