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

Sum by Month of Different Years Using SUMPRODUCT() Function

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.

A data set in Excel

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.

Number format options in Excel

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

Format Cells Dialogue box in Excel

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

Month name in place of date in Excel

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

Auto fill options in Excel

From the options, choose Fill Month.

Fill Handle options in Excel

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

Month Names in Excel

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.

SUMIFS by month formula in Excel

Do the same for the rest of the years.

SUMIFS by month of two years in Excel


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

TEXT function in Excel

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.

Dragging TEXT function through Fill Handle in Excel

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.

Name of the months in Excel

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.

SUMIF by months of all years in Excel


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.

Name of 12 months in Excel

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.

SUMPRODUCT by month of all the years together

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.

Text Fomat option in Excel

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.

Sum by Month of Different Years Using SUMPRODUCT() Function


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.

PivotTable in Excel

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.

Create PivotTable box in Excel

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

PivotTable Fields in Excel

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.

Group cells in PivotTable in Excel

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

PivotTable Grouping Box in Excel

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

Pivot Table of Sums in Excel


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.

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

1 Comment
  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.

Leave a reply

ExcelDemy
Logo