Often you may want to calculate year-to-date values in Excel.
The following examples show how to do so with two different datasets:
- A dataset that only includes one unique year.
- A dataset that includes multiple years.
Let’s jump in!
Example 1: Calculate YTD Values for Dataset with One Unique Year
Suppose we have the following dataset that contains information about sales made on various dates during one unique year:
Suppose we would like to create a new column that contains YTD (year-to-date) sales values.
We can type the following formula into cell C2 to do so:
=SUM($B$2:B2)
We can then click and drag this formula down to each remaining cell in column C:
The new YTD Sales column contains the year-to-date sales values.
For example:
- The total sales year-to-date by 1/14/2022 was 82.
- The total sales year-to-date by 4/10/2022 was 115.
- The total sales year-to-date by 5/1/2022 was 164.
And so on.
Example 2: Calculate YTD Values for Dataset with Multiple Years
Suppose we have the following dataset that contains information about sales made on various dates during multiple years:
Suppose we would like to create a new column that contains YTD (year-to-date) sales values.
First, we must type the following formula into cell C2 to extract the year from the date:
=YEAR(A2)
We can then click and drag this formula down to each remaining cell in column C:
Next, we can type the following formula into cell D2 to calculate the YTD sales values:
=SUMIF(C$2:C2,C2,B$2:B2)
We can then click and drag this formula down to each remaining cell in column C:
The new YTD Sales column now contains the year-to-date total sales values and it automatically restarts when it detects a new year.
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
