In this tutorial, you will learn How to Sum by Year in Google Sheets
- To Sum by Year in Google Sheets, you can first extract the year using the Year() function from your dataset then from the result identify the unique Year.
- Once the Unique Years are identified. You can use the SUMIF function to get the SUM by Year in Google Sheets.
How to Sum by Year in Google Sheets: Step by Step Guide
Enclosed are the steps on How to Sum by Year in Google Sheets :
Step 1: Prepare your dataset
Below is the dataset which we will use to Sum by Year in Google Sheets.
The following dataset consists of Dates and Visa Applications on that day
Step 2: Use the YEAR() function to extract Year from dates
Now we will use the Year() function to extract the Years from the available dates in our data set.
Below is the formula which we will use in our example. We will type =Year(A2) in the cell D2
=Year(A2)
To get the result on the rest of the cells in Column D . Move the cursor on top of the blue pointer in Cell D2 until it turns into a plus sign and starts dragging it downwards to apply the formula to the rest of the cells.
Step 3: Identify the Unique Year
Now we will identify the Uniques Year from the previous result using the =UNIQUE() function
We will type the formula =UNIQUE(D2:D11) in Cell E2 in our example
=UNIQUE(D2:D11)
Once we press enter we get a list of Unique Year.
Step 4: Use the SUMIF function to find the Sum by Year
Now to find the Sum by Year we will use the SUMIF(range, criterion, sum_range) function to calculate the sum of visa applications in each year.
We will type the below formula in Cell F2 in our example
In our example, we’ll type the following formula in cell F2:
=SUMIF($D$2:$D$11, E2, $B$2:$B$11)
Apply the formula downwards to the rest of the cells. Now we have the total number of applications as shown below
The above result means :
- The total number of Visa applications in the year 2019 was 28
- The total number of Visa applications in the year 2020 was 45
- The total number of Visa applications in the year 2021 was 27
- The total number of Visa applications in the year 2022 was 159
How to Sum by Year in Google Sheets(Conclusion)
In the above tutorial, we have shown you steps using which you Sum by Year in Google Sheets. We hope this tutorial on How to Sum by Year in Google Sheets was useful.
Related articles :
How to Insert Spin Button in Google Sheets ( Easy Guide )
How to Freeze Rows in Google Sheets
How to Calculate Square Root and Cube Root in Google Sheets(Quick & Easy Guide)
How to use SUMSQ Function in Google Sheets(Quick & Easy Guide )