Counting the Unique Numbers

G

Gihan Hanwella

Hi ALL,

I need to get the number of unique entries in a particular column in
an excel table using a formula to a defined cell. how can I do that?
and this number has to change when I filter the rows using other
columns.

e.g. A table with Col A and Col B has 60 rows.
Col A has 50 Unique entries. so the Number should be 50.
I filter using Col B which will give 10 rows. in these 10 Rows there
are 4 unique entries in Col A. So the Number should be 4.

Please Help. Thank you in advance.
 
T

T. Valko

Assuming the full unfiltered range is A2:A60. Adjust to suit.

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A60,ROW(A2:A60)-ROW(A2),0,1)),MATCH("~"&A2:A60,A2:A60&"",0)),ROW(A2:A60)-ROW(A2)+1)>0,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting unique values in multiple columns 4
Find largest value in column with 2 criteria 1
Excel VBA Macro to do this? 19
Excel Complex Summing 3
is Retrieving a Cell address from a cell containing a SUMPRODUCTformula a problem ? 5
Display a list of unique values from a column with multiple instances 4
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
How to get the count and average of values in reference to anotherrange of data 3

Top