Table of Contents
In Excel, you can calculate a cumulative sum by date by using the SUMIFS function. This function allows you to return a sum of data that meets multiple criteria, such as a specified date range. To use this function, you will need to enter the range of cells containing the data, the criteria range for the dates, and the criteria for the date range. The function will then return the sum of all of the values that meet the criteria. This can be used to calculate a cumulative sum by date.
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.