Vivek asks: After choosing from the Filter drop-down menus, how can you get a count of the unique values from just the visible rows? This video starts with a helper column to detect if each row is visible and then uses COUNTA, FILTER, and UNIQUE functions to solve the problem.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2399. Get a unique count of just the visible cells after applying a filter.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen. A great question today from Vivek.
This was an old video, 1034 that does count the visible rows.
Vivek wants to say he wants to count the unique values in a similar fashion.
So, apply a filter using the filter drop downs and then count just the unique values.
Alright, so here is my solution to this.
And it requires Microsoft 365 or Excel 2021 because I'm going to use the new UNIQUE and FILTER function.
I am going to first add a helper column out here that is called Visible.
And it's going to be =SUBTOTAL(.
Number 3 - that's the COUNTA a of just this one cell over here A2.
And that returns 1 to say that that the row is visible. Like that.
And of course, if I would just SUM that it would in this case give me 20 right, because there’s 20 rows.
But the beautiful thing is: (This is like “does the light in the refrigerator out when you close the door”).
If I would apply a filter here to just the Apple cells.
See that number changes, which means that all of the hidden rows are changing to 0 as well.
Alright, so our solution to get a unique count of the names over in column A.
Is we do the COUNTA of the UNIQUE of the FILTER. We are going to filter all the names in column A.
Which names do we want?
We want the ones that are visible and that will be column D. That column is either going to return one or zero.
Ones are going to be considered True and we get it.
Zeroes are false and we don't. So three closing parentheses.
And right now we have 10 unique names. If we would choose just Banana.
And we have five names: Barb, Diane, Flo, Hank Jared, Jared isn't counted. One, two, three, four, five.
Choosing something else: Apple. Andy, Chris, Ed, Gary, Ike.
Throw out the second Andy. Then Jared, and we get 6, right?
So it works. That little helper function right there.
Now again, UNIQUE and FILTER are new in 2019. They came out after Excel 2019 came out.
So you need either Microsoft 365 or the new Excel 2021.
Or of course later than that.
Dynamic arrays are discussed in this book - Excel Dynamic Arrays Straight to the Point. Click that “I” in the top right hand corner.
If you like these videos please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Thanks to Vivek for sending that question in. And thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen. A great question today from Vivek.
This was an old video, 1034 that does count the visible rows.
Vivek wants to say he wants to count the unique values in a similar fashion.
So, apply a filter using the filter drop downs and then count just the unique values.
Alright, so here is my solution to this.
And it requires Microsoft 365 or Excel 2021 because I'm going to use the new UNIQUE and FILTER function.
I am going to first add a helper column out here that is called Visible.
And it's going to be =SUBTOTAL(.
Number 3 - that's the COUNTA a of just this one cell over here A2.
And that returns 1 to say that that the row is visible. Like that.
And of course, if I would just SUM that it would in this case give me 20 right, because there’s 20 rows.
But the beautiful thing is: (This is like “does the light in the refrigerator out when you close the door”).
If I would apply a filter here to just the Apple cells.
See that number changes, which means that all of the hidden rows are changing to 0 as well.
Alright, so our solution to get a unique count of the names over in column A.
Is we do the COUNTA of the UNIQUE of the FILTER. We are going to filter all the names in column A.
Which names do we want?
We want the ones that are visible and that will be column D. That column is either going to return one or zero.
Ones are going to be considered True and we get it.
Zeroes are false and we don't. So three closing parentheses.
And right now we have 10 unique names. If we would choose just Banana.
And we have five names: Barb, Diane, Flo, Hank Jared, Jared isn't counted. One, two, three, four, five.
Choosing something else: Apple. Andy, Chris, Ed, Gary, Ike.
Throw out the second Andy. Then Jared, and we get 6, right?
So it works. That little helper function right there.
Now again, UNIQUE and FILTER are new in 2019. They came out after Excel 2019 came out.
So you need either Microsoft 365 or the new Excel 2021.
Or of course later than that.
Dynamic arrays are discussed in this book - Excel Dynamic Arrays Straight to the Point. Click that “I” in the top right hand corner.
If you like these videos please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Thanks to Vivek for sending that question in. And thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
