Table of Contents
Often you may want to sum the values of a dataset in Excel based on month and year.
For example, suppose we have the following dataset and we’d like to sum the total sales by month and year:
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 and Year from Dates
Next, type the following formula into cell C2 to extract the month and year from the date in cell A2:
=TEXT(A2, "mmm yyyy")
We can then click and drag this formula down to each remaining cell in column C:
Step 3: Find the Unique Month and Year Combinations
Next, we can type the following formula into cell E2 to extract a list of unique month and year combinations:
=UNIQUE(C2:C15)
The following screenshot shows how to use this formula in practice:
Step 4: Calculate Sum by Month and Year
Next, we can type the following formula into cell F2 to calculate the sum of sales for each date in January 2022:
=SUMIF($C$2:$C$15, E2, $B$2:$B$15)
We can then click and drag this formula down to each remaining cell in column F:
From the output we can see:
- There were 33 total sales made in January 2022.
- There were 60 total sales made in May 2022.
- There were 20 total sales made in June 2022.
And so on.