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