Excel SUMIF by Month: Formula & Examples

Formulas & functions · Updated November 22, 2026

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, MONTH will return a #VALUE! error. Convert text to dates using DATEVALUE or the Text to Columns wizard (Data > Text to Columns > Delimited > Next > Date).
  • Blank cells in the date range: MONTH of a blank cell returns 1 (January), which can skew results. Use IF to 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.