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.
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.
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 + to open the Format Cells dialog box.
Select the Date category and choose the one that matches the style we are looking for.
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.
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.