Sum by Month

Excel Formulas › Sum

Excel 365Excel 2010+Works in older Excel

Adding up values that fall in a particular month is one of the most common reporting tasks in Excel. The reliable way to do it is SUMIFS with two date boundaries — the first and last day of the month — so it works no matter how the dates are formatted and never double-counts a day.

Quick formula: to total column B for the month whose first day is in E2:
=SUMIFS(B:B, A:A, ">="&E2, A:A, "<="&EOMONTH(E2,0))
EOMONTH(E2,0) returns the last day of that month, so the two criteria fence in exactly one calendar month.

Functions used (tap for the full reference guide):

SUMIFSEOMONTHSUMPRODUCT

The example

Here is a short list of transactions. We want the total for February. The month to total is entered as a real date in E2 (the 1st of the month).

ABDE
1DateAmountMonthTotal
21/05/2026$1202/01/2026$250
31/19/2026$80
42/03/2026$200
52/21/2026$50
63/10/2026$300

The formula

The formula in E2’s neighbor (here, the total cell) is:

=SUMIFS(B2:B6, A2:A6, ">="&E2, A2:A6, "<="&EOMONTH(E2,0)) // February → 200 + 50 = 250

How it works

SUMIFS adds the numbers in the sum range only on rows where every condition is true. We give it two conditions that bracket the month:

  1. The sum range B2:B6 holds the amounts we want to add.
  2. The first condition, A2:A6, ">="&E2, keeps rows on or after the 1st of the month. The & glues the comparison operator onto the date in E2.
  3. The second condition, A2:A6, "<="&EOMONTH(E2,0), keeps rows on or before the last day of the month. EOMONTH(date, 0) returns the end of the same month.
  4. Only the two February rows satisfy both conditions, so SUMIFS returns 200 + 50 = 250.

Why not just match the month number? =SUMIFS(B:B, A:A, 2) looks tempting but it adds every February across all years, and breaks the moment your data spans more than one year. The two-boundary method is year-safe.

Try it: interactive demo

Live demo

Pick a month and watch the SUMIFS formula and its total update against the table above.

Total:

Variations

No SUMIFS? Use SUMPRODUCT (any Excel version)

In very old versions, or when you want the month and year pulled from the date directly:

=SUMPRODUCT((TEXT(A2:A6,"yyyy-mm")=TEXT(E2,"yyyy-mm"))*B2:B6) // matches year+month as text

Sum by month AND another condition

SUMIFS takes as many condition pairs as you like. Add a region column C and total February for the West region:

=SUMIFS(B2:B6, A2:A6, ">="&E2, A2:A6, "<="&EOMONTH(E2,0), C2:C6, "West")

Build a clean month list with EOMONTH

To label a monthly summary, drop the first month in a cell and fill down:

=EDATE(E2, 1) // next month's first day

Pitfalls & errors

Totals look too big. You probably matched on month number alone ("=2"), which sums that month across every year. Use the >= first-of-month and <= EOMONTH pair instead.

Dates stored as text won’t add up. If column A is left-aligned, Excel sees text, not dates, and the comparisons fail. Select the column and use Data → Text to Columns → Finish to convert, or check with =ISNUMBER(A2).

Lock your ranges before copying down. When you copy the formula beside a list of months, anchor the data: $B$2:$B$6 and $A$2:$A$6. An Excel Table (Ctrl+T) avoids this with structured references.

Practice workbook

📊
Download the free Sum by Month practice workbook
The transaction table with a live SUMIFS month total, the SUMPRODUCT alternative, and 4 practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

How do I sum by month and year together?
That is exactly what the two-boundary SUMIFS does. Because the criteria are real dates (first and last day of a specific month in a specific year), it only totals that one month of that one year.
Can I sum by month without a helper column?
Yes. The SUMIFS with >=first-of-month and <=EOMONTH needs no helper column. The SUMPRODUCT version with TEXT(range,"yyyy-mm") also works with no helper column.
How do I total by month from a column of dates that span years?
Use the SUMIFS two-boundary method with a real first-of-month date for each row of your summary. It is year-aware, unlike matching the month number alone.
What's the difference between SUMIF and SUMIFS here?
SUMIFS allows multiple conditions, which we need to fence both ends of the month. SUMIF only allows one condition, so you would need a clumsier single criterion. Use SUMIFS.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: SUMIFS with multiple criteria · Running total · Count by criteria (COUNTIFS)

Function references: SUMIFS · EOMONTH · SUMPRODUCT