There’s no MONTH criteria in SUMIF, so you bound the month with two dates instead: =SUMIFS(B2:B103,A2:A103,">="&DATE(2022,1,1),A2:A103,"<="&EOMONTH(DATE(2022,1,1),0)). The DATE function gives you the first of the month and EOMONTH with a 0 gives you the last, so together they capture exactly that month of that year.
Build both dates with DATE rather than typing them as text — a text date depends on regional settings. To sum a month across every year in the data, use SUMPRODUCT with MONTH instead: =SUMPRODUCT(B2:B103,--(MONTH(A2:A103)=1)) totals every January regardless of year. The double minus converts the TRUE/FALSE results into 1s and 0s so they can be multiplied.
To drive either one from a cell rather than hard-coded numbers, put the month start in a cell and reference it: =SUMIFS(B2:B103,A2:A103,">="&E2,A2:A103,"<="&EOMONTH(E2,0)), which lets you drag the formula down a list of month-start dates and get all twelve totals.
To sum values based on the month, when you have dates in a column, you can use SUMIFS or SUMPRODUCT. With SUMIFS, you can refer to dates that fall within a specific month of the year. And with the SUMPRODUCT, you can sum for a particular month irrespective of the year.
Our data show dates in three years, 2021, 2022, and 2023. And we will use both formulas here.
Use SUMIFS to Sum Values by Month of a Year
Follow these steps to write this formula:
- First, in a cell, enter the SUMIFS, and then, in the first argument, refer to the range from where you want to get the sum.
- After that, in the second argument (criteria_range1), specify the range where you have the dates.
- Next, in the third argument (criteria), enter (“>=”&DATE(2022,1,1)) to specify the starting date of the month.
- Now, in the criteria_range2, again refer to the range with dates. And in the criteria2, enter (“<=”&EOMONTH(“01-Jan-2022”,0)).
- In the end, type the closing parenthesis and hit Enter to get the result.
And once you hit Enter, it returns the sum of values where the date is in the month of Jan 2022.
=SUMIFS(B2:B103,A2:A103,">="&DATE(2022,1,1),A2:A103,"<="&EOMONTH("01-Jan-2022",0))
How Does This Formula Work?
To understand this formula, you need to split it into three parts:
In the first part, you have specified the range from where you need to get the sum.
In the second part, you have referred to the range where you have dates, and then you have used the DATE function to create a valid date of 01-Jan-2022.
With the DATE function, you also used the greater than and equal to operators. And when you do this, it tells SUMIFS to only sum the values for dates equal to or greater than 01-Jan-2022.
In the third part, you have again referred to the date range.
In the criteria, you have used the EOMONTH function with the date 01-Jan-2022 and 0 as months, which returns the last day (date) of January, 31-Jan-2022. Along with that, you have the less than and equal to operators.
This tells SUMIFS to sum values for the dates below or equal to 31-Jan-2022.
With the above two criteria, you have a date range between 01-Jan-2022 and 31-Jan-2022, and the formula will sum up all the values between these two dates.
There are two things to keep in mind with this formula. First, “01-Jan-2022” is a text date, which Excel reads according to your system’s date settings, so it’s safer to use the DATE function inside EOMONTH as well. Second, if your dates also have a time, a date like 31-Jan-2022 6:00 PM is greater than 31-Jan-2022, so “<=” leaves it out. To include the full last day, use “<” with the first day of the next month (EDATE adds one month):
=SUMIFS(B2:B103,A2:A103,">="&DATE(2022,1,1),A2:A103,"<"&EDATE(DATE(2022,1,1),1))
Take the Month and Year from Cells
Instead of typing the date in the formula, you can enter the year in cell D1 (like 2022) and the month number in E1 (like 1), and then build the dates from them. Now you only need to change D1 and E1 to get the total for another month:
=SUMIFS(B2:B103,A2:A103,">="&DATE(D1,E1,1),A2:A103,"<"&EDATE(DATE(D1,E1,1),1))
SUMPRODUCT to Sum Values Based on the Month
And if you want to sum values for a month irrespective of the year, you can use the SUMPRODUCT and MONTH functions.
=SUMPRODUCT(B2:B103,--(MONTH(A2:A103)=1))
You have used the MONTH function in this formula and referred to the entire date range. After that, you have tested it with 1, which means checking dates for January.
After that, you have the double minus sign to convert TRUE into 1 and FALSE into 0.
In the end, SUMPRODUCT multiplies both of the arrays (Quantity and 0-1), and returns the sum for the dates where the month is January.
Watch out for empty cells in the date column. Excel treats an empty cell as 0, which is the date 0-Jan-1900, and MONTH returns 1 for it. So, the formula adds the values of rows with no date to January. To leave them out, add one more condition that checks the date isn’t blank:
=SUMPRODUCT(B2:B103,--(MONTH(A2:A103)=1),--(A2:A103<>""))
And if you want a specific month of a specific year with SUMPRODUCT, add a YEAR condition as well:
=SUMPRODUCT(B2:B103,--(MONTH(A2:A103)=1),--(YEAR(A2:A103)=2022))
Sum All Months with a Pivot Table
If you need the total for every month at once, a pivot table is quicker than writing a formula for each month. Insert a pivot table, put the date field in Rows and the quantity in Values. Excel groups the dates by Years, Quarters and Months automatically in recent versions; if it doesn’t, right-click a date in the pivot table ➜ Group and select Months and Years.
Frequently Asked Questions
Why does my sum by month formula return 0?
Most of the time, the dates are stored as text, so SUMIFS can’t compare them with real dates. Check a date cell with ISNUMBER (formula below): if it returns FALSE, convert the column to real dates first (for example, with Data ➜ Text to Columns).
=ISNUMBER(A2)
Can I use SUMIF instead of SUMIFS to sum by month?
Not directly, because a month needs two conditions (start date and end date), and SUMIF takes only one. You can subtract two SUMIFs, but SUMIFS is simpler.
How do I sum by month name?
Use SUMPRODUCT with TEXT. TEXT returns the short month name for each date, and the formula sums the rows where it matches:
=SUMPRODUCT(B2:B103,--(TEXT(A2:A103,"mmm")="Jan"))
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.