Count unique, non-hidden cells only

cview

New Member
Joined
Aug 15, 2012
Messages
2
I have a column with names of products, some of which appear multiple times. As I apply filters in other columns to my data, I want to be able to count the number of unique, non-hidden names that remain with a formula. Is there any way to do this so that the number of unique non-hidden names updates itself each time I change the filter settings?

Thank you! I'm a first-time poster, though I've used existing threads in the past for help. Any help would be greatly apprecited.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Gary's Student

Well-known Member
Joined
Aug 4, 2012
Messages
1,014
Say we have data in column A from A2 thru A100 and the data is filtered. To count the visible uniques in column A, use:
Code:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),MATCH("~"&A2:A100,A2:A100&"",0)),ROW(A2:A100)-ROW(A2)+1)>0,1))
This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,491
Messages
5,414,854
Members
403,549
Latest member
CascadeDiver

This Week's Hot Topics

Top