Google Sheets offers a variety of ways to sum amounts based on values in other cells. These techniques can be helpful for many different tasks, such as calculating sales by region, product, or customer; tracking expenses by category; or summarizing financial data. The solutions in this article will start simple and get more complex as we go.
We will use this table of data for all the examples. This table, along with the solutions, are in this linked Sheet if you want to follow along.
First, we’ll start with a technique that uses two functions that you may already know.
Contents
Example 1 – Sum Values With Traditional Functions
Our goal is to find how many cases there are of each Item. We will end up with a list of the Items
in one column and total Cases
in the next column. We’ll use two functions to get this done. First, we’ll use the UNIQUE function which returns a list of unique values from a range
.
UNIQUE Syntax: =UNIQUE(range)
We’ll give the UNIQUE function the range where the Items
reside, which is A2:A11
. This function will return a list of the unique Items
from this range. Notice we did not include row 1
.
⚠️ You need to type the headers when using this function.
Now we have a list of the four unique Item
we want to sum. Now, we will use the SUMIF function.
The SUMIF function is one of the most common ways to sum amounts based on values in other cells. It takes three arguments: the range
of cells containing the criterion, the criterion
used to filter the cells, and the range of cells to be summed (sum_range
).
SUMIF Syntax: =SUMIF(range, criterion, [sum_range])
=SUMIF($A$2:$A$11,E2,$B$2:$B$11)
This formula in cell F2 will return the sum of all values in column B where the corresponding value in column A is equal to Wrench
. Since we used fixed cell references for the range
and sum_range
, you can copy and paste the formula into cells F3
, F4
, and F5
, and the two ranges will not change, but the Item
will.
That is the simplest way to add values based on other cells. Let’s look at using a pivot table next, which gives us more flexibility than using UNIQUE and SUMIF.
Example 2 – Sum Values Using Pivot Tables
Pivot tables are a powerful tool for summarizing and analyzing data. You can use them to sum amounts based on values in other cells, as well as to calculate other statistical measures, such as averages, medians, and counts.
For this example, we will use a pivot table to create the same output as in step one, but without any formulas.
To create a pivot table, select the range of data you want to analyze, and then click on the Insert menu and select Pivot table. Use the OmniPivot addon to use more than one range. The Insert and Pivot table menu combination opens the pivot table editor.
In the pivot table editor, drag the fields you want to use to summarize the data to the Rows, and Values areas. For this example, to sum the Items in column B for each unique Item
listed in column A, you would drag the Item
field to the Rows area and the Cases
field to the Values area in the pivot table editor.
Once you have created the pivot table, you can filter the data and calculate subtotals and grand totals as needed. Let’s look at another flexible solution next.
Example 3 – Sum Values Using the QUERY function
The QUERY function is a powerful tool for filtering and sorting data. You can also use it to sum amounts based on values in other cells.
For example, to sum the Cases
in column B for the Items
listed in column A, you would use the following formula:
=QUERY(A1:C11,"select A, sum(B) group by A",1)
This formula will return the sum of all of the Cases
in column B where the corresponding Item
in column A is equal to that Item
. Unlike example 1, one formula creates these results. Also, the QUERY lets you use SQLlike commands, opening up new possibilities not available in traditional spreadsheet functions.
Example 4 – Sum with More than One Condition
You may wonder why you should use a more complex solution like the QUERY function in example 3. That question is easy to answer by showing what happens when you add another condition. Let’s look at summing the values by Item
then by Warehouse
. This extra level of summing would need many more formulas if you were using SUMIF and UNIQUE, like in example 1. But, using the QUERY function, you still need only one formula.
=QUERY(A1:C11,"select A, C, sum(B) group by A, C",1)
We added another layer simply by adding a few letters to the QUERY formula.
Video Tutorial
Conclusion
Google Sheets offers a variety of ways to sum amounts based on values in other cells. The best method to use will depend on the specific needs of your task.
To create a list of unique values from a range of cells, you can use the UNIQUE function. You can then use this list with SUMIF to sum amounts based on the unique values in another column. Pivot tables are a powerful tool for summarizing and analyzing data, and you can also use them to sum amounts based on values in other cells. The QUERY function is a powerful tool for filtering and sorting data, and you can also use it to add amounts based on values in other cells.
Related Articles

Sum Values Based on Another Cell’s Values in Google Sheets
Learn how to sum amounts based on the values in other cells.

Conditionally Count Unique Values in Google Sheets
Three techniques to count unique values based on specified criteria.

Summarize Unique Values In Google Sheets
Learn how to list, count, and sum unique values from a table of data.