October 22, 2022

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.

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.

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

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 **Format Cells** dialog box.

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

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.

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

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.

If you want to learn additional formulas, I recommend this article: Sum sales by year.

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?

Excel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.

Copyright © 2017-2022 ExcelExplained.com

**Session expired**

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.