Table of Contents
Calculating the sum by year in Google Sheets is a simple and efficient way to organize and analyze data. To do so, you can use the SUMIF function, which allows you to specify a certain criteria to sum up data from a specific column. First, create a new column next to your data and label it as “Year”. Then, use the YEAR function to extract the year from the date column and populate the “Year” column. Next, use the SUMIF function to sum up the data based on the criteria of the year. This will give you the total sum for each year. This method can be useful for tracking annual expenses, sales, or any other data that is organized by date.
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:
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.
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can I calculate the sum by year in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-by-year-in-google-sheets/
stats writer. "How can I calculate the sum by year in Google Sheets?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-by-year-in-google-sheets/.
stats writer. "How can I calculate the sum by year in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-by-year-in-google-sheets/.
stats writer (2024) 'How can I calculate the sum by year in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-calculate-the-sum-by-year-in-google-sheets/.
[1] stats writer, "How can I calculate the sum by year in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I calculate the sum by year in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.