Google Sheets Function Cascading

Google Sheets functions have a really convenient trick. It's very similar chaining, which is something a lot of programming languages use. You can summarize data and fill multiple cells in one step. I am going to demonstrate it here without using a lot of code or jargon. If you can use Excel, you can do this. (Bonus: if you're used to Excel's "array" functions, you don't have to do anything like that.)


First, let's create a very simple Google spreadsheet: This data could be collected from a website or some other process. The point is we are collecting repeated data with different values and we'd like to summarize that data.

The first thing we want is a list of just the unique values from column A. If we were using Excel for this, we'd probably copy the data into a column and use the "Remove Duplicates" function. This is a manual process. With Sheets we can do this with one function: Unique


Unique takes a list of values and returns a list of unique values. (Sounds obvious, right?) Notice that no matter how long that list gets, our function will return a list of unique values while skipping row one. Unique writes the values in multiple cells starting with the cell with the function. The number of cells used depends on the number of unique values found.

Notice that we have a list with unique values, but it would be nice if these were alphabetized, right?


This is where function cascading comes in. We can pass the results of unique into another function: sort. Sort is very powerful and can sort larger datasets by multiple columns. We don't need all that power for what we're trying to do. We can just pass our entire unique function into sort.

Note that our unique values are now sorted alphabetically.


Now we can begin to summarize the data for each person. To do this we use a function you are probably more familiar with: sumif.

Sumif uses the name in column E as a key to summarize the data. This setup is useful because if a new person shows up in the original data, that person will automatically be inserted into the list and the summary in column F will continue to work. 

The big caveat here is that you must make sure the sumif function in column F is copied far enough down the sheet to handle all the potential entries. 

A more realistic example


Let's say your data is slightly more complex. Each person in the data set now also has a department and we want to summarize by department. 

To do this, we'll cascade another useful function: filter. We can use filter to only return items from column A where column B is some value we want. In this example I only want people who are in the engineering department.


We take the output of the filter function and pass it into the unique function. Everything else stays example the same. If we change cell F1 to Accounting, the list of people would change and the data in column G would update automatically. 

As you can see, Google Sheets function cascading is very useful for automation. It's also an elegant way to build a sheet that keeps up with incoming data.