This tutorial explains how to calculate a cumulative sum grouped by date in Excel.
Step 1: Create the Data
First, let’s create a dataset that shows the total sales of some store by date:
Step 2: Calculate Overall Cumulative Sum
Next, we can create a column that displays the overall cumulative sum of sales.
First, type =B2 in cell C2.
Then, type the following formula in cell C3:
=SUM($B$2:B3)
Then drag this formula down to every remaining cell in column C:
Step 3: Calculate Cumulative Sum by Date
Next, we can create a column that displays the cumulative sum of sales by date.
First, type =B2 in cell D2.
Then, type the following formula in cell D3:
=IF(A3=A2, B3+D2, B3)
Then drag this formula down to every remaining cell in column D:
The result is a column that displays the cumulative sales grouped by each date.
For example:
- The cumulative sales for 1/1/2022 is: 5, 8, 15, 27
- The cumulative sales for 1/2/2022 is: 7, 12, 2
- The cumulative sales for 1/3/2022 is: 15
- The cumulative sales for 1/4/2022 is: 3, 10, 19, 29
Notice that the values in the cumulative sum column restart for each new date.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Calculate the Sum by Group in Excel
How to Calculate Relative Frequency in Excel
How to Calculate Cumulative Frequency in Excel