Table of Contents
To sum by month in Excel, start by selecting the range of cells to sum or inputting the range in the formula. Then, enter the SUMIFS formula into the cell where you want the sum to appear. Inside the formula, add the criteria range which will be used to identify the month you want to sum. Then add the criteria or the month you want to sum. Lastly, add the sum range which is the range of cells that will be summed. Press enter to calculate the sum and the total sum by month will appear in the cell.
Often you may want to sum the values of some dataset in Excel based on month.
For example, suppose we have the following dataset and we’d like to sum the total sales by month:
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, enter the data values into Excel:
Step 2: Extract the Month from Dates
Next, we need to use the =MONTH() function to extract the month from each date.
In our example, we’ll type the following formula in cell D2:
=MONTH(A2)
We’ll then drag and fill this formula down to every remaining cell in column D:
Step 3: Find the Unique Months
Next, we need to use the =UNIQUE() function to produce a list of unique months.
In our example, we’ll type the following formula in cell F2:
=UNIQUE(D2:D10)
Step 4: Find the Sum by Month
Next, we will use the SUMIF(range, criterion, sum_range) function to find the sum of the sales made during each month.
In our example, we’ll type the following formula in cell G2:
=SUMIF($D$2:$D$10, F2, $B$2:$B$10)
We’ll then drag and fill this formula down to the remaining cells in column G:
This tells us:
- There were 117 total sales made in January.
- There were 50 total sales made in February.
- There were 111 total sales made in March.