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.
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. 👍
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.
Mike, may I remind you of one of the rare videos on my YT-channel? The most recent one (TEXTO-002). I also posted the single cell formula -all wrapped into a single LAMBDA- on the Mr.Excel message board earlier today. I changed the name a little bit since the video. Here's the link: www.mrexcel.com/board/threads/distinctcountcolumnvisiblecells.1167773/
I knew there was a way to do it is a single cell. I woke up this morning and as I was making breakfast, Boom! I remembered: =ROWS(UNIQUE(FILTER(fSales[SalesRep],SUBTOTAL(3,OFFSET(fSales[[#Headers],[SalesRep]],SEQUENCE(ROWS(fSales[SalesRep])),))))) Single cell OFFSET and SUBTOTAL magic : )
@@GeertDelmulle That is beautiful!!!!!!!!!! So rad that you remembered SUBTOTAL and OFFSET. Where did you learn this? It is soooooooooo old school. I think I learned it from Don Keotee or Domenique at the Mr Excel Message Board back in about 2007. I tried to use SUBTOTAL yesterday but I mistakenly put OFFSET in the wrong argument, then this morning during breakfast, BOOM!!!! My eyes got wide and I thought: "Duh!!!!", OFFSET goes into ref1... lol So happy to be on a Team with You and Mr Excel : )
@@excelisfun Some time ago, I needed this function in that dashboard we talked about so many times before. I decided to Get Digital Help, found a really Old School formula over there and transformed it into what I posted here. The final formula doesn't look at all like the one I started from, but this one SUBTOTAL-OFFSET trick is conceptually unchanged. The rest (using FREQUENCY, etc.) is unrecognizable and not at all needed anymore, because we now have UNIQUE and FILTER. The SUBTOTAL-OFFSET trick however is no less than crucial to make it work, and in that step, the final argument of "1" is crucial again. So, in conclusion: this entire formula hinges on that single "1". ;-) I indicated that quite meticulously in my short video "TEXTO-002" on my "The Exceleer" RUclips-channel. Too bad, I didn't use the FILTER-function there -- still too much influenced by the Old School way of doing things! (although, it did give me a renewed and very strong appreciation of the simple IF function! The things you can do with the IF function in a context of Dynamic Arrays... Oh My! :-) If (pun intended) you want to know more about that: head over to the Mr.Excel Message Board, the LAMBDA-section and look at many of my LAMBDAs. When it comes to DAs: IF rules! (and INDEX, and...) Anyway, I perfected the formula and put it in the comment section here, completely LAMBDA-fied, and all. Provided my comments don't get removed. It's also on the Mr.Excel Message Board as a LAMBDA-thread.
@@excelisfun Hi Mike/Geert.. thanks guys for the inspiration to tinker with this. You guys are mad scientists for sure!! In case any other viewers want an assist, here is Mike's formula applied to Bill's list in the video (not a CTRL+T table): =COUNTA(UNIQUE(FILTER(A2:A21,SUBTOTAL(3,OFFSET(A1,SEQUENCE(ROWS(A2:A21)),))))). Also, you guys might want to see my exchange above with Bill re. using SUBTOTAL in the same range of a list where you activate the Filter buttons.. seems to not filter the last item in the list for some reason.. AGGREGATE solves the problem. Just interesting to know these things. Thanks again and Thumbs up!! Go Team :))
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!!
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!!! : )
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...😉
Hi Bart - There are two similar buttons to add to the QAT. The one from the Data tab is "Filter". This one is "AutoFilter" and does the equivalent of Filter by Selection when you are in a data set that is not a Table. The complete video on Filter by Selection is ruclips.net/video/rtkNu8Uj9C0/видео.html
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?
If you look at the help for the SUBTOTAL function, you'll be inclined to do exactly that: 103. Is it a fluke or a bug that SUBTOTAL(3... also works? In my LAMBDA-function I used 103 just to be sure (and future-proof?). Here's the link: www.mrexcel.com/board/threads/distinctcountcolumnvisiblecells.1167773/
Because he did filter shortcut with macros, so if he stands on Banana, A1:D20 would filter for banana, but he forgot to add it for 21st row, so its outside of filter
@@tornikekvinikadze7981 I replicated the worksheet and filtered manually (without macro) and I get the same incorrect filtering, but only if I have all four columns A-D, like MrExcel. With just cols A and B (i.e. cols C & D deleted) it filters correctly. Also, with just cols A-C (i.e. col D deleted) it filters correctly. It must be the formulae in col D that are making it filter weirdly.
Whoa! You caught me!!!! I hate using Tables, but in this case, my using a range instead of a table made sure that the new row 21 that I typed later did not get included in the filter. Nice catch. I sure did not see it when I was editing!
F$%^%^&* Awesome!!!!! 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
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 :)
Thanks for posting this but there are a couple of typos. If you are able to edit your comment, could you change this: =IF(COUNTIF($A$2:A2,A2)>1,0,SUBTOTAL,3,A2) to this: =IF(COUNTIF($A$2:A2,A2)>1,0,SUBTOTAL(3,A2))
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?
This was my mistake. I originally made the dataset to end at row 20 and tested it with 20 rows. But then just before recording the video, I decided having 20 rows would be better, so I added row 21 but did not reapply the filter. A lot of my friends tell me that I am asking for trouble by not using Ctrl+T tables and this is proof that they are correct.
@@MrXL On my dataset, I did not use a CTRL+T table, just a standard list. I also varied the number of rows in the table. If the SUBTOTAL column is part of the range of the list and the filter button is visible at the top of its column, then filtering on the other columns always ignores the last item in the list. As soon as you move the SUBTOTAL column outside of the range of the data/list and reset the filter buttons, then they filter normally. Seems like a bug, not necessarily related to CTRL+T tables, since I'm not using them. Interested to see if you can reproduce the effect when not using tables. Never a dull moment with EXCEL :))
@@wayneedmondson1065 OMG. That truly is annoying. Why, Excel? Why? My cheap work-around: IN CELL A22, type "END OF TABLE" and leave the SUBTOTAL column blank (actually, it will autofill that column, so you will have to delete it. Better: Change =SUBTOTAL(3,A2) to =AGGREGATE(3,5,A2) and copy down. The AGGREGATE doesn't trigger this bizarre behavior.
@@MrXL Interesting. Actually, I just figured out how Mike did his single cell version using OFFSET and SEQUENCE which I guess solves the problem, as no SUBTOTAL helper column needed then. But for most mortal Excel users, they would use a helper column to solve. So, good to know that might be an issue with filtering if the helper has a formula that somehow causes this "last on list not filtered" behavior. I wonder if it is related only to SUBTOTAL or if it happens with other functions in use within the range of a list being filtered. I'll investigate some more. Let you know if I discover any other anomalies :))
@@MrXL FYI, this is Mike's formula that would work on your non-CTRL+T list: =COUNTA(UNIQUE(FILTER(A2:A21,SUBTOTAL(3,OFFSET(A1,SEQUENCE(ROWS(A2:A21)),)))))
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
Thanks for letting me know. This is horrible. Can you e-mail me with details about the seller? I will mail you a copy. My e-mail is pub at mrexcel dot com
I am trying to Match Stock prices on Date Basis for example what was is the stock price of a particular stock before 2 or 3 years & if that day is a Holiday then it should give me next working days price & if it is Saturday and Sunday it should show me Monday or next working day price because sometimes Monday is a Holiday for Rolling Returns. Can You show me Easiest way ? .For example what was the price of Apple before 3 years? If it is hoilday give me next working day's price for Apple stock. Thanks.
To get three years ago, you might use =TODAY()-365*3 or perhaps =DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY())) or something more clever than that. To make sure that this does not fall on a Saturday, wrap your formula in WORKDAY: =WORKDAY(DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY()))-1,1) You can put a list of holidays in a range, perhaps Z1:Z30 and use: =WORKDAY(DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY()))-1,1,$Z$1:$Z$30)
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.
How???? Why??
Thank you.
This formula is scratching the right parts of my brain 😂
Mr. Excel is really the No 1 clever guy in the Excel community. Thanks for sharing your wisdom 👍❤🌹
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. 👍
Thank you for your help, I spent a lot time looking for this, was more easy than I thought
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.
Mike, may I remind you of one of the rare videos on my YT-channel?
The most recent one (TEXTO-002).
I also posted the single cell formula -all wrapped into a single LAMBDA- on the Mr.Excel message board earlier today.
I changed the name a little bit since the video.
Here's the link: www.mrexcel.com/board/threads/distinctcountcolumnvisiblecells.1167773/
I knew there was a way to do it is a single cell. I woke up this morning and as I was making breakfast, Boom! I remembered:
=ROWS(UNIQUE(FILTER(fSales[SalesRep],SUBTOTAL(3,OFFSET(fSales[[#Headers],[SalesRep]],SEQUENCE(ROWS(fSales[SalesRep])),)))))
Single cell OFFSET and SUBTOTAL magic : )
@@GeertDelmulle That is beautiful!!!!!!!!!! So rad that you remembered SUBTOTAL and OFFSET. Where did you learn this? It is soooooooooo old school. I think I learned it from Don Keotee or Domenique at the Mr Excel Message Board back in about 2007. I tried to use SUBTOTAL yesterday but I mistakenly put OFFSET in the wrong argument, then this morning during breakfast, BOOM!!!! My eyes got wide and I thought: "Duh!!!!", OFFSET goes into ref1... lol
So happy to be on a Team with You and Mr Excel : )
@@excelisfun Some time ago, I needed this function in that dashboard we talked about so many times before.
I decided to Get Digital Help, found a really Old School formula over there and transformed it into what I posted here.
The final formula doesn't look at all like the one I started from, but this one SUBTOTAL-OFFSET trick is conceptually unchanged.
The rest (using FREQUENCY, etc.) is unrecognizable and not at all needed anymore, because we now have UNIQUE and FILTER.
The SUBTOTAL-OFFSET trick however is no less than crucial to make it work, and in that step, the final argument of "1" is crucial again. So, in conclusion: this entire formula hinges on that single "1". ;-)
I indicated that quite meticulously in my short video "TEXTO-002" on my "The Exceleer" RUclips-channel.
Too bad, I didn't use the FILTER-function there -- still too much influenced by the Old School way of doing things!
(although, it did give me a renewed and very strong appreciation of the simple IF function! The things you can do with the IF function in a context of Dynamic Arrays... Oh My! :-) If (pun intended) you want to know more about that: head over to the Mr.Excel Message Board, the LAMBDA-section and look at many of my LAMBDAs. When it comes to DAs: IF rules! (and INDEX, and...)
Anyway, I perfected the formula and put it in the comment section here, completely LAMBDA-fied, and all. Provided my comments don't get removed.
It's also on the Mr.Excel Message Board as a LAMBDA-thread.
@@excelisfun Hi Mike/Geert.. thanks guys for the inspiration to tinker with this. You guys are mad scientists for sure!! In case any other viewers want an assist, here is Mike's formula applied to Bill's list in the video (not a CTRL+T table): =COUNTA(UNIQUE(FILTER(A2:A21,SUBTOTAL(3,OFFSET(A1,SEQUENCE(ROWS(A2:A21)),))))). Also, you guys might want to see my exchange above with Bill re. using SUBTOTAL in the same range of a list where you activate the Filter buttons.. seems to not filter the last item in the list for some reason.. AGGREGATE solves the problem. Just interesting to know these things. Thanks again and Thumbs up!! Go Team :))
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!!
Absolute lifesaver. Many thanks!
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!!! : )
Hi John - Nice. But the question was how to count the UNIQUE values...
Amazing! Thank you.
Really Nice Solution...Thank You Mr Excel :)
Great formula thanks Bill
Thanks Sir
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...😉
Hi Bart - There are two similar buttons to add to the QAT. The one from the Data tab is "Filter". This one is "AutoFilter" and does the equivalent of Filter by Selection when you are in a data set that is not a Table. The complete video on Filter by Selection is ruclips.net/video/rtkNu8Uj9C0/видео.html
Well done Bill!
What if I want unique values (not count) from visible range?
Great solution!
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?
Hi Mr Excel, great video as always. Can we use =SUBTOTAL(103,B2:B21) ?
If you look at the help for the SUBTOTAL function, you'll be inclined to do exactly that: 103.
Is it a fluke or a bug that SUBTOTAL(3... also works?
In my LAMBDA-function I used 103 just to be sure (and future-proof?).
Here's the link: www.mrexcel.com/board/threads/distinctcountcolumnvisiblecells.1167773/
They were trying to count the UNIQUE values.
@@MrXL oops missed that part :)
I noticed this formula counts an extra row at the very end of the filter
agree... when filtering, the last row is added, making the counts wrong
Hi - this is great. But why does 'Cherry' always show visible at the bottom (row 21) when you're filtering on Banana or Apple?
Because he did filter shortcut with macros, so if he stands on Banana, A1:D20 would filter for banana, but he forgot to add it for 21st row, so its outside of filter
@@tornikekvinikadze7981 I replicated the worksheet and filtered manually (without macro) and I get the same incorrect filtering, but only if I have all four columns A-D, like MrExcel. With just cols A and B (i.e. cols C & D deleted) it filters correctly. Also, with just cols A-C (i.e. col D deleted) it filters correctly. It must be the formulae in col D that are making it filter weirdly.
Whoa! You caught me!!!! I hate using Tables, but in this case, my using a range instead of a table made sure that the new row 21 that I typed later did not get included in the filter.
Nice catch. I sure did not see it when I was editing!
F$%^%^&* Awesome!!!!!
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
My version is for office 365 and thie formular cannot work, could you please suggest me?
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 :)
Thanks for posting this but there are a couple of typos. If you are able to edit your comment, could you change this:
=IF(COUNTIF($A$2:A2,A2)>1,0,SUBTOTAL,3,A2)
to this:
=IF(COUNTIF($A$2:A2,A2)>1,0,SUBTOTAL(3,A2))
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?
This was my mistake. I originally made the dataset to end at row 20 and tested it with 20 rows. But then just before recording the video, I decided having 20 rows would be better, so I added row 21 but did not reapply the filter. A lot of my friends tell me that I am asking for trouble by not using Ctrl+T tables and this is proof that they are correct.
@@MrXL On my dataset, I did not use a CTRL+T table, just a standard list. I also varied the number of rows in the table. If the SUBTOTAL column is part of the range of the list and the filter button is visible at the top of its column, then filtering on the other columns always ignores the last item in the list. As soon as you move the SUBTOTAL column outside of the range of the data/list and reset the filter buttons, then they filter normally. Seems like a bug, not necessarily related to CTRL+T tables, since I'm not using them. Interested to see if you can reproduce the effect when not using tables. Never a dull moment with EXCEL :))
@@wayneedmondson1065 OMG. That truly is annoying. Why, Excel? Why? My cheap work-around: IN CELL A22, type "END OF TABLE" and leave the SUBTOTAL column blank (actually, it will autofill that column, so you will have to delete it. Better: Change =SUBTOTAL(3,A2) to =AGGREGATE(3,5,A2) and copy down. The AGGREGATE doesn't trigger this bizarre behavior.
@@MrXL Interesting. Actually, I just figured out how Mike did his single cell version using OFFSET and SEQUENCE which I guess solves the problem, as no SUBTOTAL helper column needed then. But for most mortal Excel users, they would use a helper column to solve. So, good to know that might be an issue with filtering if the helper has a formula that somehow causes this "last on list not filtered" behavior. I wonder if it is related only to SUBTOTAL or if it happens with other functions in use within the range of a list being filtered. I'll investigate some more. Let you know if I discover any other anomalies :))
@@MrXL FYI, this is Mike's formula that would work on your non-CTRL+T list: =COUNTA(UNIQUE(FILTER(A2:A21,SUBTOTAL(3,OFFSET(A1,SEQUENCE(ROWS(A2:A21)),)))))
Thanks for the formula, is it possible to have unique count without helper column
I am sure it is possible, and someone smarter than me will likely post it here over the next day or so.
@@MrXL You are a very humble person. I learned a lot from you, thanks for all the excel lessons
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
Thanks for letting me know. This is horrible. Can you e-mail me with details about the seller? I will mail you a copy. My e-mail is pub at mrexcel dot com
I am trying to Match Stock prices on Date Basis for example what was is the stock price of a particular stock before 2 or 3 years & if that day is a Holiday then it should give me next working days price & if it is Saturday and Sunday it should show me Monday or next working day price because sometimes Monday is a Holiday for Rolling Returns. Can You show me Easiest way ? .For example what was the price of Apple before 3 years? If it is hoilday give me next working day's price for Apple stock. Thanks.
To get three years ago, you might use =TODAY()-365*3 or perhaps =DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY())) or something more clever than that.
To make sure that this does not fall on a Saturday, wrap your formula in WORKDAY:
=WORKDAY(DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY()))-1,1)
You can put a list of holidays in a range, perhaps Z1:Z30 and use:
=WORKDAY(DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY()))-1,1,$Z$1:$Z$30)
DistinctCountVisibleCells(ColumnVector)
=LAMBDA(Source,LET(
RowdexFromZero,SEQUENCE(ROWS(Source),,0),
IsVisible,SUBTOTAL(103,OFFSET(Source,RowdexFromZero,0,1)),
COUNTA(UNIQUE(FILTER(Source,IsVisible)))
))
(ColumnVector)