Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will show you how you can perform the SUMIF by month operation in Excel. That means you will learn how to sum data by month using SUMIF() and SUMIFS() functions in Microsoft Excel.
Download Practice Workbook
Download this workbook and practice while going through the article.
2 Methods to Perform SUMIF by Month Operation in Excel
This is the dataset for today’s article. We have the sales amount for a company with the dates. I will use it and explain the methods.
1. Sum by Month of Each Year in Excel
First of all, we shall detect the sum by month of the same year.
That means, we shall determine the total sales for May 2019 and May 2020 separately, and so on.
We will use a combination of the SUMIFS and EOMONTH functions here.
Steps:
- First of all, enter the dates in E5:E16.
- Then, go to the Home
- After that, select the icon (see the image).
- Format Cells box will appear.
- Then, select the Custom
- After that, write “mmmm” in the type box.
- Then, click OK.
- Excel will show the month’s name in E5:E16.
- Now, go to F5 and write down the following formula
=SUMIFS($C$5:$C$25,$B$5:$B$25,">"&E5,$B$5:$B$25,"<"&EOMONTH(E5,0))
- Then, press ENTER to get the output.
- After that, use the Fill Handle to AutoFill up to F16.
- Similarly, calculate the total sales for 2020.
2. Sum by Month of All Years in Excel
Now we will calculate the total sales of each month comprising all the years.
That means, now we shall calculate the total sales for June 2019 and June 2020 together. This method will need the TEXT function.
Steps:
- First of all, go to D5 and write down the following formula
=TEXT(B5,"mmmm")
- Then, press ENTER to get the output.
- After that, use the Fill Handle to AutoFill up to D16.
- Then, go to G5 and write down the following formula
=SUMIF($D$5:$D$25,F5,$C$5:$C$25)
- Then, press ENTER to get the output.
- After that, use Fill Handle to AutoFill up to G16.
Apply SUMPRODUCT Function as Alternative
An alternative to the SUMIF by month operation is the use of the SUMPRODUCT function. I am going to explain that method step by step here.
Case 1: Sum by Month of Each Year
First of all, I will show how to calculate the sales for each year respectively.
Steps:
- Go to F5 and write down the following formula
=SUMPRODUCT($C$5:$C$25,((TEXT($B$5:$B$25,"mmmm")=$E5)*(TEXT($B$5:$B$25,"yyyy")=F$4)))
- Then, press ENTER to get the output.
- After that, use Fill Handle to AutoFill up to G16.
Case 2: Sum by Month of All Years
Now I will show how to calculate the total sales for a month.
Steps:
- First of all, go to F5 and write down the following formula
=SUMPRODUCT($C$5:$C$25,(--(TEXT($B$5:$B$25,"mmmm")=$E5)))
- Then, press ENTER to get the output.
- After that, use the Fill Handle to AutoFill up to F16.
Use PivotTable Feature as Alternative
The next alternative is the use of the PivotTable feature.
Steps:
- First of all, select the range B4:C25.
- Then, go to the Insert
- After that, select PivotTable.
- A box will appear.
- Choose the location of your PivotTable.
- Then, click OK.
- Excel will create a pivot table.
- Then, from the PivotTable Fields, drag the Date and Total Sales in the Rows and Values Field.
- Excel will by default show the Sum of Total Sales.
- Thus, your pivot table will look like this.
- Next, select any date.
- Right-click your mouse to bring the context menu.
- Then, select Group.
- A grouping box will appear.
- Then, group the dates by month.
- After that, select the starting and ending dates.
- Finally, click OK.
- Excel will show month-wise sales.
Things to Remember
- Use absolute reference to lock a cell.
- The TEXT function takes a value and a format as the argument and returns the value in that format.
Conclusion
In this article, I have explained how to perform the SUMIF by month operation. There are 2 alternatives too. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.
I’m trying to sum by the current month on my spreadsheet so I can see what I have closing that month. The sumproduct formula used to work but no longer as I just get a #Value! error. I currently have =SUMPRODUCT((MONTH(I3:I43)=MONTH(B1))*(AA3:AA43)), where I3:I43 is the close date column, B1 is the simple formula =today(), and AA3:AA43 is my gross income.
Hello BRYAN,
This is Aung, one of the writers/developers in ExcelDmey. I’ve looked into your matter. What you need to do is just modify your formula. Choose any one from the below 2 formulas:
=SUMPRODUCT(AA3:AA43,(–(TEXT(I3:I43,”mmmm”)=TEXT(B1,”mmmm”))))
=SUMPRODUCT(AA3:AA43,(–(MONTH(I3:I43)=MONTH(B1))))
Both should work perfectly fine.
Good luck.