How to SUMIF by Month in Excel (With Alternative Options)

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

• 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

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

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

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.

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

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.

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.

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.

Further Readings

• Sum Values Based on Date in Excel