Excel SUMIFS Month and Year: Formula Guide

Formulas & functions · Updated November 22, 2026

To sum values by month and year in Excel, use the SUMIFS function with two criteria: one that checks the month and one that checks the year. For example, with dates in A2:A100 and amounts in B2:B100, the formula =SUMIFS(B2:B100,A2:A100,">="&DATE(2024,1,1),A2:A100,"<="&EOMONTH(DATE(2024,1,1),0)) sums all amounts for January 2024. This approach is robust because it uses real date boundaries, avoiding text-based pitfalls.

Why SUMIFS Needs Date Boundaries

Excel stores dates as serial numbers, so comparing a date to a text string like "January" will not work. The standard way to filter by month and year is to set a lower bound (first day of the month) and an upper bound (last day of the month). The EOMONTH function returns the last day of a given month, making it easy to construct the upper bound. For example, EOMONTH(DATE(2024,1,1),0) returns 31-Jan-2024. Using >= and <= with these two dates captures every date in that month, including the first and last day.

Step-by-Step: Basic SUMIFS by Month and Year

Assume your data has dates in column A (A2:A100) and values in column B (B2:B100). To sum for January 2024, follow these steps:

  1. In a cell, enter the first day of the month you want, for example DATE(2024,1,1).
  2. Use EOMONTH to get the last day: EOMONTH(DATE(2024,1,1),0).
  3. Write the formula: =SUMIFS(B2:B100,A2:A100,">="&DATE(2024,1,1),A2:A100,"<="&EOMONTH(DATE(2024,1,1),0)).

This returns the sum of all values in B where the date in A is between 1-Jan-2024 and 31-Jan-2024. If you have a cell (say D1) containing a date, you can reference it: =SUMIFS(B2:B100,A2:A100,">="&DATE(YEAR(D1),MONTH(D1),1),A2:A100,"<="&EOMONTH(D1,0)). This makes the formula dynamic—change D1 to any date, and the sum updates for that month.

Using a Helper Column for Simpler Formulas

If you prefer a shorter formula, create a helper column that extracts the month and year as a text string. For example, in column C, enter =TEXT(A2,"YYYY-MM") for each row. Then use SUMIFS with a single criterion: =SUMIFS(B2:B100,C2:C100,"2024-01"). This is easier to read but requires an extra column. The helper column approach is useful when you want to sum multiple months quickly by changing the criterion text. Note that TEXT returns a text value, so the criterion must be text in the same format.

Using SUMPRODUCT for Flexibility

Another option is SUMPRODUCT, which can handle arrays without helper columns. For example, to sum for January 2024: =SUMPRODUCT((A2:A100>=DATE(2024,1,1))*(A2:A100<=EOMONTH(DATE(2024,1,1),0))*B2:B100). This works by multiplying boolean arrays (TRUE/FALSE) with the values. The advantage is that you can add more conditions without extra columns, but it is less intuitive than SUMIFS and can be slower on very large datasets. For most users, SUMIFS is the recommended choice because it is built for this purpose.

Common Problems and How to Fix Them

  • Dates stored as text: If your dates are text, SUMIFS will not match. Convert them using DATEVALUE or the Text to Columns wizard (Data > Text to Columns).
  • Wrong date format: Ensure your system date format matches your formula. Use DATE(2024,1,1) instead of typing "1/1/2024" to avoid regional issues.
  • Blank cells or errors in date column: SUMIFS ignores blank cells, but errors like #VALUE! can cause problems. Clean your data first.
  • Using wildcards with dates: Wildcards like * do not work with dates. Stick to date comparisons.

Comparing SUMIFS with PivotTables

PivotTables offer a visual way to sum by month and year without formulas. To create one, select your data, go to Insert > PivotTable, drag the date field to Rows, and the value field to Values. Then right-click the date field, choose Group, and select Months and Years. This groups dates automatically. The advantage is that you can expand and collapse levels, and it updates with a refresh. However, PivotTables are not dynamic like formulas—you must refresh when data changes. For a quick one-off analysis, a PivotTable is often easier; for a recurring report, SUMIFS with a dynamic date cell is more automated.

Using SUMIFS with a Cell Reference for Month and Year

To make your formula fully dynamic, put a date in a cell (e.g., D1) and use it as the reference. For example, with D1 containing 15-Mar-2024, the formula =SUMIFS(B2:B100,A2:A100,">="&DATE(YEAR(D1),MONTH(D1),1),A2:A100,"<="&EOMONTH(D1,0)) will sum all of March 2024. This works because DATE(YEAR(D1),MONTH(D1),1) gives the first day of the month, and EOMONTH(D1,0) gives the last day. You can also use a dropdown list (Data Validation) to let users pick a month, making the report interactive.

Frequently Asked Questions

Can I use SUMIFS to sum by year only?

Yes, use =SUMIFS(B2:B100,A2:A100,">="&DATE(2024,1,1),A2:A100,"<="&DATE(2024,12,31)) for the year 2024. Alternatively, use SUMPRODUCT((YEAR(A2:A100)=2024)*B2:B100) but that is an array formula and may be slower.

Why does my SUMIFS return 0 even though I have matching dates?

Check that your date column is truly dates, not text. Select the column and see if the Number Format shows a date. If it is text, use DATEVALUE to convert. Also, ensure your criteria use the correct date serial numbers—typing "1/1/2024" as text will not work.

Can I sum by month and year using a single cell with a date?

Yes, as shown above, reference a cell containing any date. The formula extracts the month and year from that cell. This is the most flexible approach.

Is there a way to sum by month across multiple years?

Yes, you can use SUMPRODUCT with MONTH and YEAR functions. For example, to sum all Januarys regardless of year: =SUMPRODUCT((MONTH(A2:A100)=1)*B2:B100). But if you need a specific year, include the year condition as well.

What if my dates are in a different column than the values?

SUMIFS allows you to specify different ranges for criteria and sum range. For example, if dates are in A and values in C, use =SUMIFS(C2:C100,A2:A100,">="&DATE(2024,1,1),A2:A100,"<="&EOMONTH(DATE(2024,1,1),0)). The sum range is the first argument.

Can I use SUMIFS with a month name like "January"?

Not directly, because Excel does not recognize month names as dates. You would need to convert the month name to a date using DATEVALUE or a lookup table. For example, DATEVALUE("1 "&"January"&" 2024") returns a date, but it is simpler to use the DATE function as shown.

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.