Table of Contents
To calculate YTD values in Google Sheets, you can use the SUMIFS formula to add up the values from the beginning of the year till the selected date. The formula takes in the range of cells to be summed, the criteria of the dates to be considered (in this case, the dates falling within the year), and the range of cells containing the dates. This will give you the total sum of values for the year, up until the selected date. You can then use this formula in a separate cell to calculate the YTD value for any specific date.
Often you may want to calculate year-to-date values in Google Sheets.
The following examples show how to do so with two different datasets:
Dataset 1: A dataset that only includes one unique year.
Dataset 2: 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/4/2023 was 25.
- The total sales year-to-date by 1/5/2023 was 38.
- The total sales year-to-date by 4/15/2023 was 46.
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 Google Sheets: