Combine Filters In One Column in Google Sheets
Learn how to combine multiple filters into a single column in Google Sheets, allowing for dynamic updates based on selected criteria. This tutorial demonstrates the use of array formulas and concatenation techniques to create a clear summary of sales data.
We have different months, different regions, different sales for different salesmen, and we just want to see a little summary. In one. Column of who got what sales in which month. And if I just change B2 to March, the sales automatically update. How did I do this? We're combining two filters. If you know the filter already, we can do something like this filter , and we can take a whole range , or maybe we just want these two ranges , D and E, and we can. Filter it by some condition . We're going to say actually the like the G column is equal to March. We can do D colon E here we just need to get the same row sizes. So we see here this is the same thing we have over here on the left, but it is in two columns. We're getting filter for the D column and filter for the E column.
So how can we combine these into one column.? We're going to create two different filters, one for the D column, one for the E column, and then combine them with array formula . We also have to use a little bit of a trick here, which is the, ampersand, which is a kind of concatenate without using concatenate formula or join formula . The thing with join formula is that it does not work with array formula. So we have to use some other way to concatenate or combine or join these together. First, we're going to filter just the rep, and we're going to filter it by G column is equal to March. But we don't have to use the text March, we can use a cell reference here, which is going to be B2. And now we have just the names. But we can also change this D to E, and we get the sales.
We can change it to F and it will get the region. But for our case, we just need D colon D first. And then we're going to use array formula around it. We're going to wrap this filter with array formula , but we're going to add an ampersand and another filter , which is going to be the E column. And let's look at that. Have a name. I think we use misspelled array formula . There we go. Okay. So now we have the parts, but they're next to each other, a little too close, so we need to separate them so we can read this easier. So instead of just ampersand, another ampersand. And in between that, we'll put. Then we have some leaning quotes which is going to be a space, pipe, space. There we have Andy, Dwight, Jim. We have each of their sales and we want to add at the end another ampersand and the word space sales. And so now we get a nice little summary in column B of the filters that we want.
We can even add more to this. we can do ampersand. Filter , say F by the same G colon G equals B2. Let's put a space here and after that we have resales in the, put another ampersand space region. So now we have a nice little report that's very easy to see, very easy to read.