How to Sum by Year in Google Sheets (With Example)


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:

Google Sheets sum by year

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

Leave a Reply

Your email address will not be published. Required fields are marked *