Sum Values Based on the Month (SUMIF)

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:

1. 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.
2. After that, in the second argument (criteria_range1), specify the range where you have the dates.
3. Next, in the third argument (criteria), enter (“>=”&DATE(2022,1,1)) to specify the starting date of the month.
4. Now, in the criteria_range2, again refer to the range with dates. And in the critera2, enter (“<=”&eomonth(“01-jan-2022”,0)).
5. In the end, type the closing parentheses 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 Jan’2022.

=SUMIFS(B2:B103,A2:A103,">="&DATE(2022,1,1),A2:A103,"<="&EOMONTH("01-Jan-2022",0))

How Does This Formula Works?

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 greater than and equal operators. And when you do this, it tells the SUMIFS to only sum for the date 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 EMONTH function with the dates 01-Jan-2022 and 0 as months, which returns the last day (date) of the January month, 31-Jan-2022. Along with that, you have the lower than and equal operator.

This tells SUMIF to sum values for the dates below or equal to 31-Jan-2033.

With the above two criteria, you have a date range between 01-Jan-2022 to 31-Jan-2022, and the formula will sum up all the values between these two dates.

SUMPRODUCT to Sum Values Based using 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, test 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 return you the sum for the dates where the month is January.