Often you may want to sum the values of some dataset in Excel based on quarter.
For example, suppose we have the following dataset and we’d like to sum the total sales by quarter:
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: Enter the Quarters
Next, we’ll manually create a list of the four quarters in a new column:
Step 3: Calculate the Sum by Quarter
Next, we’ll type the following formula into cell E2 to calculate the sum of sales for the first quarter:
=SUMPRODUCT((ROUNDUP(MONTH($A$2:$A$14)/3,0)=D2)*$B$2:$B$14)
We’ll then drag and fill this formula down to the remaining cells in column E:
From the output we can see:
- There were 71 total sales made in the first quarter.
- There were 38 total sales made in the second quarter.
- There were 130 total sales made in the third quarter.
- There were 66 total sales made in the fourth quarter.
We can verify these values are correct by manually calculating the sum of sales for a given quarter.
For example, the total sales in the first quarter (January, February, March) can be calculated as:
First Quarter Sales: 30 + 12 + 15 + 14 = 71
This matches the value calculated by our formula.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Sum by Year in Excel
How to Sum by Month in Excel
How to Sum by Week in Excel