How to Perform SUMIF by Month Operation in Excel

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

• 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

• 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

• 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

• 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

• 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.

Further Readings

• Sum Values Based on Date in Excel