Excel Unique Count of Visible Rows - Episode 2399

Microsoft Excel Tutorial: How to Count Unique Values of Visible Cells after Applying a Filter in Excel | MrExcel Podcast.

Welcome to the MrExcel Podcast, where we bring you the latest tips and tricks for mastering Excel. In this episode, we will be discussing how to get a unique count of just the visible cells after applying a filter. This is a great question from Vivek, who wanted to know how to count the unique values in a similar fashion to our previous video on counting visible rows.

To achieve this, we will be using the new UNIQUE and FILTER functions, which are only available in Microsoft 365 or Excel 2021. First, we will add a helper column called "Visible" and use the SUBTOTAL function to count the visible rows. This will return a value of 1 for visible rows and 0 for hidden rows. By applying a filter, we can see that the number changes, indicating that the hidden rows are now counted as 0.

Next, we will use the COUNTA function to count the unique values in column A. We will use the UNIQUE function to filter the names in column A and only count the ones that are visible, which is indicated by the helper column we created. This will give us an accurate count of the unique names, even when filters are applied.

It's important to note that the UNIQUE and FILTER functions were introduced after Excel 2019, so you will need Microsoft 365 or Excel 2021 to use them. If you want to learn more about dynamic arrays and other advanced Excel techniques, check out our book "Excel Dynamic Arrays Straight to the Point" by clicking the "I" in the top right-hand corner. And don't forget to like, subscribe, and ring the bell to stay updated on our latest videos. Thanks to Vivek for sending in this question and thank you for tuning in. We'll see you next time for another netcast from MrExcel.

Table of Contents
(0:00) Problem Statement: Count Unique of Visible Cells after filtering in Excel
(0:31) Adding Visible Column with SUBTOTAL function
(1:22) COUNTA of UNIQUE of FILTER
(2:18) Microsoft 365
(2:29) Buy the book and Wrap-up

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
how to count only visible cells in excel
how to count pnlyu visible cells in excell
how to count only visible rows in excel
how to count visible rows in excel
how to only count visible cells in excel
how to find visible cells on excel
how to only sum visible cells in excel
how to sum only visible cells in excel
how to total only visible cells in excel
how to total visible cells in excel
how to sum only visible cells when filtering in excel
how to sum visible cells only in excel
how to sum visible cells only in excel?
how to use visible cells only in excel

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.

[kebincui]

Mr. Excel is really the No 1 clever guy in the Excel community. Thanks for sharing your wisdom 👍❤🌹

[zharkynaitashpolotova]

SUM(IF(FREQUENCY(IF(SUBTOTAL(3, OFFSET(A9, ROW(A9:A711)-ROW(A9), , 1)), IF(A9:A711<>"", MATCH("~"&A9:A711, A9:A711&"", 0))), ROW(A9:A711)-ROW(A9)+1), 1)) I use this formula to count unique filtered ones without helper column, and put it on top for each column. You can change the numbers for rows depending from your report. It is quite heavy and makes report work slower. However if you have numerous columns that you filter, probably this formula could help replacing option to create helper columns for all rest needed columns to be filtered.

[mattritchie]

That is a fantastic workaround for something that should really be simply function to toggle on filtering to apply to remove from formulas and counts. Thank you so much! I have been pulling my hair out for hours trying to do this but once added in your "Visible" column it was so simple made me feel a little dumb (and made my partner roll her eyes after the language and desk thumps coming from my side of our home office all afternoon). Making sure your data is formatted as table allows you to hide the column after safe in the knowledge every new row will have it and you won't even know it's there. Duly Liked/Subscribed to come here first next time I have an issue. 👍

[danysoty]

Thank you for your help, I spent a lot time looking for this, was more easy than I thought

[johnborg]

Thanks Mr Excel. If I had that issue, I would have gone to Subtotal(103, range). That handles filtered and hidden rows. But that was fun watching!!! : )

[wayneedmondson]

Hi Mr. Excel. Awesome trick there! I see below that Geert and Mike have a single cell solution.. nice!! Always fun and good learning at your channel. Thanks for sharing :)) Thumbs up!!

[excelisfun]

Great video, Mr Excel!!! Elegant solution!!! I can not seem to find a way to roll the formula up into a single cell. SUBTOTAL and AGGREGATE are the only two functions that I know of that can deal with filtered rows, and I can't get them to simulate the helper column in a single cell. I just have to think that there is a way, but I can not see it.

[darrylmorgan]

Really Nice Solution...Thank You Mr Excel :)

[yokerpl]

For those of you who're using older Excel version and thus are missing "UNIQUE" function. You can mimic it by nesting MrExcel's funtion in IF and COUNTIF. It would look like it: =IF(COUNTIF($A$2:A2, A2)>1, 0, SUBTOTAL(3, A2)) and then you SUM it same was as presented on the clip around 1:00. Please notice how first A2 in COUNTIF is locked, whereas the other isn't. It's important :)

[gawalisantosh]

What if I want unique values (not count) from visible range?

[melvioferrao]

F$%^%^&*
Why is the helper column necessary?
I would think that excel could the unique values without the helper column, otherwise it fails the description of the function.
Either, reconciliation is easier now

[barttitulaerexcelbart]

Thanks Bill, What is the name of that filter button you used in the QAT? It is not autofilter....If you hoovered just one more second I could have seen that name as quick info, delaying the video doesn't help...😉

[DssSmyth]

Thanks, that works if you just have a filtered list but I can't seem to get the same results if I am wanting to count a unique list of visible rows in a table. Does this not work when using a table?

[abhinavarya]

Hi Mr Excel, great video as always. Can we use =SUBTOTAL(103, B2:B21) ?

[prron]

I noticed this formula counts an extra row at the very end of the filter

[kornwipaponganan]

My version is for office 365 and thie formular cannot work, could you please suggest me?

[iankr]

Hi - this is great. But why does 'Cherry' always show visible at the bottom (row 21) when you're filtering on Banana or Apple?

[excelwithhemantjain]

Thanks for the formula, is it possible to have unique count without helper column

[wayneedmondson]

Hi Mr. Excel. I just noticed something odd about the filtering. When you have the SUBTOTAL formula in column D and you apply a filter to column B, the last record in the list is not subject to the filter. You can see it in minute 1:12 of your video.. filtered on Apple, but Jared and Cherry appear in the filtered list. Same for minute 1:56 (Banana filter.. still see Jared/Cherry) and 2:06 (Apple filter again.. still see Jared/Cherry). If you move the Visible column outside the range of your data table (insert to move it to column E and make sure that the filter buttons are visible only on the data table), then filtering works normally again on the main data table. If you move it back so that it is part of the range of your data table, the last item does not get filtered. Weird huh?

[TS-yyjb]

I purchased your unmasking excel 2021 on Amazon and have been sent a pirated copy despite spending £20 on it, wanted to bring it to your attention. The seller has made a cheap photocopy and is selling on Amazon, where should I report this and where can I buy the original version please