How to sum values by month in Excel [step-by-step example]

Often you will need to sum values by month and return subtotals. Excel is great at this, and there are several methods to achieve this using SUMIFS, SUMPRODUCT, or a pivot table, just to name a few.

In this article, I will show you how to sum values by month in Excel using the SUMIFS function. You can download the exercise file if you want to follow along.

Let’s assume we have a table containing daily sales values (columns B and C), and we want to calculate the total sales for each month.

Table with sales

Step 1: Create the list of months to use

The first thing you need to do is create a list of months for which you want to calculate the total sales. To do this, insert the date corresponding to the first day of each month (e.g., for November 2017, use 11/1/2017). I have done this in column E.

Insert the first date of each month in a separate table

Note: You can also use the DATE function to create each date.

Step 2: Change the date format

The next step is to change the date format to something more friendly, like Nov-17. To do this, select the entire range of dates in the column and press CTRL + 1 to open the Format Cells dialog box.

Select the date format from the Format Cells dialog box

Select the Date category and choose the one that matches the style we are looking for.

Adjusted date format

Step 3: Sum values by month

Using the SUMIFS function, we can now sum values by month. To do this, we need to apply multiple criteria and use the EOMONTH function.

We now need to match dates against two conditions.

The first criterion we need to check is whether the dates are greater than or equal to the start date (i.e., 11/1/2017). The second criterion to be checked is if the dates are lower than or equal to the end date, which is the last day of the month (i.e., 11/30/2017).

The final SUMIFS formula from cell F3 looks like this:

=SUMIFS($C$3:$C$34, $B$3:$B$34, ">="&E3, $B$3:$B$34, "<="&EOMONTH(E3, 0))

Drag or copy the formula to the rest of the cells to get the values for each month.

Sum by month in Excel using SUMIFS formulas

You can also sum by month with an alternative formula that uses the EDATE function. The principles are the same as the above example, but you need a slightly different second condition because the EDATE function will calculate the first day of the following month. The final formula is shown below:

=SUMIFS($C$3:$C$34, $B$3:$B$34, ">="&E3, $B$3:$B$34, "<"&EDATE(E3, 1))

What to do next?

While this article explained how to sum by month in Excel using formulas, I strongly encourage you to learn how to use a pivot table. They are excellent at summarizing data by years, quarters, months, or days.

About me

My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.

If you'd like to get in touch, you can contact me on LinkedIn.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

>