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+ to open the 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.
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?
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.