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:
- In a cell, enter the first day of the month you want, for example
DATE(2024,1,1). - Use
EOMONTHto get the last day:EOMONTH(DATE(2024,1,1),0). - 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,
SUMIFSwill not match. Convert them usingDATEVALUEor 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:
SUMIFSignores 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
- Excel Sales Tracking Template by Product: How to Build One
- Excel Reverse Mortgage Calculator Spreadsheet
- Excel Reverse Lookup with Multiple Criteria: How-To
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Paste Special: Copy Values, Formulas, and MoreTo use Paste Special in Excel for Microsoft 365 on Windows, copy the source cells, select the destination,…
- How to Divide in Excel: Formulas and the QUOTIENT FunctionTo divide values in Excel, enter a formula with the forward slash operator, such as =A2/B2. Excel does…
- Count Unique Values in an Excel ColumnTo count distinct, nonblank values in an Excel column, use =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))) in Excel for Microsoft 365 or Excel…
- SQL Server Split Function: STRING_SPLIT ExplainedSQL Server’s built-in split function is STRING_SPLIT, which turns a delimited string into a table of values. Use…
- Excel ROUND DOWN to Whole Number: Formulas and ExamplesTo round a number down to a whole number in Excel, use ROUNDDOWN with zero decimal places. If…
- Number Value in Excel: Use the VALUE FunctionIn Excel, use the VALUE function to convert text that represents a number into a number Excel can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.