Today I will be showing how you can sum data by month using **SUMIF()** and **SUMIFS()** functions in Microsoft Excel.

**Download Practice Workbook**

**How to SUMIF by Month in Excel**

Let us have a look at this data set. We have the Sales Record of some dates of the years 2019 and 2020 of a company named Ernesto Group.

Now we shall detect the sum of the total sales by month using the **SUMIF()** function of Excel.

**Case 1. Sum by Month of the Same Year**

First of all, we shall detect the sum by month of the same year.

That means, we shall determine the total sales of the month of May 2019 and May 2020 separately, and so on.

We will use two functions here.** SUMIFS() **and **EOMONTH()**

**SUMIFS()**

**Syntax**

`=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2]...)`

- Takes a range of cells called
**sum_range**. - Takes one or more range of cells called
**criteria_range**and one or more**criteria**alongside. - Returns the sum of the cells of the
**sum_range**whose adjacent cells of**criteria_range**maintain the given**criteria**.

In order to know more about **SUMIFS()** function, visit this link.

**EOMONTH()**

**Syntax**

`=EOMONTH(start_date, month)`

- Takes a date called
**start_date**and a number called**month**. - Creates a new month by adding the number called
**month**with the month of the date called**start_date**. - Then return the last date of the new month.

For example,

`EOMONTH(6-Mar-19,2) = 31-May-19`

In order to know more about the **EOMONTH()** function, visit this link.

Now letâ€™s determine the total sales of the months of the year 2019 first.

First of all, select a cell and enter the date, 1/1/2019.

Then go to **Home>Number Format>More Number Format** in Excel Toolbar.

You will get the **Format Cells** Dialogue box. In the Category box, choose **Custom**. Then in the Type box, write** mmmm**.

Then click OK. You will find **January** written in place of the date **1/1/2019**.

Then drag the **Fill Handle** up to 12 cells. After dragging click on the **Auto Fill Options**.

From the options, choose **Fill Month**.

You will find the names of the 12 months inserted beautifully like this.

Then in the adjacent cell to January, write this formula

`=SUMIFS($C$4:$C$24,$B$4:$B$24,">"&E4,$B$4:$B$24,"<"&EOMONTH(E4,0))`

This calculates the total sales of January 2019.

And then drag the **Fill Handle** again to get the total sales of each month of the year 2019.

Do the same for the rest of the years.

**Case 2: Sum by Month of Different Years**

Now we shall calculate the total sales of each month comprising all the years.

That means, now we shall calculate the total sales of the months June 2019 and June 2010 together.

To do this, take a separate column and enter this formula in the first cell of that column.

I enter this formula in cell **D4** and name the column as **Month Name**.

`=TEXT(B4, "mmmm")`

**Note: **The **TEXT()** function takes a value and a format as the argument and returns the value in that format.

Here, it takes a date as an argument and returns only the month name of the date.

Now drag the **Fill Handle** to insert this formula to the rest of the cells.

Then select a cell and write January.

Then drag the **Fill Handle** up to 12 more cells to get the names of all the months of a year.

Now in the adjacent cell to January, insert this formula

`=SUMIF($D$4:$D$24,F4,$C$4:$C$24)`

This calculates the total sales of the month January of all the years.

And then drag the** Fill Handle** to get the total sales of the rest of the months.

**Alternative Options**

There are a few alternative options you can use to achieve the same goal.

In fact, they are a bit easier sometimes.

**Option 1: Using SUMPRODUCT() Function**

Alternatively, you can do this using the **SUMPRODUCT()** function of Excel.

First of all, for calculating the total sales of each month of different years together,

Write down the names from each month, January to December, in a column like this.

Then insert this formula in the adjacent cell of January.

`=SUMPRODUCT($C$4:$C$24,(--(TEXT($B$4:$B$24,"mmmm"=$E4)))`

And then drag the **Fill Handle** to get the total sales of each month.

And for calculating the sales by the months of each year separately, write the names of the years in the adjacent rows and save them as text format from **Home>Number Format>Text.**

Then insert this formula in cell **F4**.

`=SUMPRODUCT($C$4:$C$24,((TEXT($B$4:$B$24,"mmmm")=$E4)*(TEXT($B$4:$B$24,"yyyy")=F$3)))`

And drag it through both row and column to get the total sales of each month of the years separately.

**Option 2: Using PivotTableÂ **

Using **PivotTable** is a comparatively easier option to calculate the sum by month.

First, select any cell within your data. Then go to **Insert>PivotTable>PivotTable.**

Click on **PivotTable**. You will get the **Create PivotTable** Box.

Check **Existing Worksheet.**

Then in the Location box, insert the cell reference where you want the **PivotTable**.

I insert cell **E4**.

Then click **OK**. You will get the PivotTable created. Put checks into **Date** and **Total Sales**.

Coming back to the worksheet, you will see the dates have been inserted into a column called **Row Labels**, and the total sales have been inserted into a column called **Sum of Total Sales**.

Right click on any date and choose **Group.**

Then in the **Grouping Box**, select **Months** and click **OK**.

And you will get the total sales arranged beautifully by months in a **PivotTable**, like this.

**Conclusion**

Using these methods, you can calculate the sum of any data by month in Excel. Do you have any questions? Feel free to ask in the comment section.

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.