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.

dataset for sumif by month


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.

Method for sumif by month from each year

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

Method for sumif by month from each year

  • After that, use the Fill Handle to AutoFill up to F16.

  • Similarly, calculate the total sales for 2020.

Method for sumif by month from each year


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.

Method for sumif by month from all year

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

Method for sumif by month from all year

  • Then, press ENTER to get the output.

  • After that, use Fill Handle to AutoFill up to G16.

Total sales by month using sumif by month


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)))

SUMPRODUCT alternative for sumif by month

  • Then, press ENTER to get the output.

  • After that, use Fill Handle to AutoFill up to G16.

SUMPRODUCT alternative for sumif by month


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.

SUMPRODUCT alternative for sumif by month

  • After that, use the Fill Handle to AutoFill up to F16.

SUMPRODUCT alternative for sumif by month


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.

PivotTable Alternative for SUMPRODUCT alternative for sumif by month

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

PivotTable Alternative for SUMPRODUCT alternative for sumif by month

  • Thus, your pivot table will look like this.

  • Next, select any date.
  • Right-click your mouse to bring the context menu.
  • Then, select Group.

PivotTable Alternative for SUMPRODUCT alternative for sumif by month

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

PivotTable Alternative for SUMPRODUCT alternative for sumif by month


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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

2 Comments
  1. 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.

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo