Often you may want to sum the values of a dataset in Google Sheets based on year.
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, enter the values for a dataset that show the total sales of some product on various dates:
Step 2: Extract the Years from Dates
Next, we need to use the =YEAR() function to extract the year from each date.
In our example, we’ll type the following formula in cell D2:
=YEAR(A2)
We’ll then drag and fill this formula down to every remaining cell in column D:
Step 3: Find the Unique Years
Next, we need to use the =UNIQUE() function to produce a list of unique years.
In our example, we’ll type the following formula in cell F2:
=UNIQUE(D2:D11)
This will produce a list of unique years:
Step 4: Find the Sum by Year
Next, we will use the SUMIF(range, criterion, sum_range) function to find the sum of the sales made during each year.
In our example, we’ll type the following formula in cell G2:
=SUMIF($D$2:$D$11, F2, $B$2:$B$11)
We’ll then drag and fill this formula down to the remaining cells in column G:
This tells us:
- There were 42 total sales made in 2020.
- There were 12 total sales made in 2021.
- There were 60 total sales made in 2022.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
How to Sum by Month in Google Sheets
How to Use SUMIF with OR in Google Sheets
How to Use SUMIF Contains in Google Sheets
