Google Sheets offers a range of techniques for adding amounts based on the values of other cells. These methods can be applied to various tasks, such as calculating sales by region, tracking expenses by category, or summarizing financial data. In this article, we will explore different methods, starting from simple ones and gradually moving to more complex ones.
Example 1 – Adding Values with Traditional Functions
Our goal is to find out how many cases there are for each item. To achieve this, we will create a list of items in one column and the total cases in the next column. For this purpose, we will use two functions that you might already be familiar with.
The first function we’ll use is UNIQUE, which returns a list of unique values from a range. We’ll use the UNIQUE function with the range where the items are listed, from A2 to A11. This function will provide us with a list of unique items in that range. Note that we didn’t include row 1.
⚠️ Remember to include the headers when using this function.
Now we have a list of the four unique items that we want to add up. Next, we’ll use the SUMIF function.
The SUMIF function is one of the most common ways to add amounts based on the values of other cells. It takes three arguments: the range of cells containing the criterion, the criterion used to filter cells, and the range of cells to be summed (sum_range).
This formula in cell F2 will give the sum of all values in column B when the corresponding value in column A is “Wrench”. Since we used fixed cell references for the range and sum_range, you can copy and paste the formula in cells F3, F4, and F5, and both ranges will remain unchanged while the item changes.
This is the simplest way to add values based on other cells. Now let’s see how to create a pivot table, which offers more flexibility than using UNIQUE and SUMIF.
Example 2 – Adding Values Using Pivot Tables
Pivot tables are powerful tools for summarizing and analyzing data. You can use them to add amounts based on the values of other cells, as well as calculate other statistical measures such as averages, medians, and grand totals.
In this example, we’ll use a pivot table to achieve the same result as in the previous step, but without using formulas.
To create a pivot table, select the data range you want to analyze, then click on the Insert menu and select Pivot Table. If you want to use more than one range, you can use the OmniPivot add-on. The combination of the Insert menu and Pivot Table opens the pivot table editor.
In the pivot table editor, drag the fields you want to use for summarizing the data into the Rows and Values areas. For this example, to add up the items from column B for each unique item listed in column A, you need to drag the Item field into the Rows area and the Cases field into the Values area of the pivot table editor.
Once you have created the pivot table, you can filter the data and calculate subtotals and grand totals based on your needs. Now let’s look at another flexible solution.
Example 3 – Adding Values Using the QUERY Function
The QUERY function is a powerful tool for filtering and sorting data. You can also use it to add amounts based on the values of other cells.
For example, to add up the cases from column B for the items listed in column A, you would use the following formula:
This formula will give the sum of all cases from column B when the corresponding item in column A matches that item. Unlike Example 1, only one formula creates these results. Additionally, the QUERY function allows you to use SQL-like commands, opening up new possibilities that are not available with traditional spreadsheet functions.
Example 4 – Adding Values with Multiple Conditions
You might wonder why you should use a more complex solution like the QUERY function in Example 3. This question is easy to answer by showing what happens when you add another condition. Let’s take the example of summing values by item and then by warehouse. To achieve this additional sum, you would need a lot more formulas if you were using SUMIF and UNIQUE, as in Example 1. But by using the QUERY function, just one formula is enough.
We added an additional level by simply adding a few letters to the QUERY formula.
Conclusion
Google Sheets provides various methods to add amounts based on the values of 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 add amounts based on the unique values in another column. Pivot tables are powerful tools for summarizing and analyzing data, and you can also use them to add amounts based on the values of 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 the values of other cells.
Learn more about Google Sheets and its amazing features at Crawlan.com.
