If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to count the number of unique entries in a filtered column
Perhaps this may have been answered before. I used formulas from the
Discussion Groups but did not get the right answer. I have the following filtered data (alphanumeric) exactly: A100 A102 A104 A103 A108 A104 A102 A102 A103 A109 The total number of entries is 10 and the unique number of entries is 6.What formula should be used to calculate the unique number of entries? Thanks in advance |
#2
|
|||
|
|||
How to count the number of unique entries in a filtered column
Try this
=SUMPRODUCT((A1:A10"")/COUNTIF(A1:A10,A1:A10&"")) If this post helps click Yes --------------- Jacob Skaria "learnlearn52" wrote: Perhaps this may have been answered before. I used formulas from the Discussion Groups but did not get the right answer. I have the following filtered data (alphanumeric) exactly: A100 A102 A104 A103 A108 A104 A102 A102 A103 A109 The total number of entries is 10 and the unique number of entries is 6.What formula should be used to calculate the unique number of entries? Thanks in advance |
#3
|
|||
|
|||
How to count the number of unique entries in a filtered column
If your filtered list is in A2:A200 try with CTRL+SHIFT+ENTER:
=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A200) -ROW(A2),)),MATCH(A2:A200,A2:A200,0)),ROW(A2:A200)-ROW(A2))) "learnlearn52" wrote: Perhaps this may have been answered before. I used formulas from the Discussion Groups but did not get the right answer. I have the following filtered data (alphanumeric) exactly: A100 A102 A104 A103 A108 A104 A102 A102 A103 A109 The total number of entries is 10 and the unique number of entries is 6.What formula should be used to calculate the unique number of entries? Thanks in advance |
#4
|
|||
|
|||
How to count the number of unique entries in a filtered column
Thanks Lori. You are awesome!!! Keep up the good work.
"Lori" wrote: If your filtered list is in A2:A200 try with CTRL+SHIFT+ENTER: =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A200) -ROW(A2),)),MATCH(A2:A200,A2:A200,0)),ROW(A2:A200)-ROW(A2))) "learnlearn52" wrote: Perhaps this may have been answered before. I used formulas from the Discussion Groups but did not get the right answer. I have the following filtered data (alphanumeric) exactly: A100 A102 A104 A103 A108 A104 A102 A102 A103 A109 The total number of entries is 10 and the unique number of entries is 6.What formula should be used to calculate the unique number of entries? Thanks in advance |
#5
|
|||
|
|||
How to count the number of unique entries in a filtered column
Jacob, thanks for your help. Kepp up the good work.
"Jacob Skaria" wrote: Try this =SUMPRODUCT((A1:A10"")/COUNTIF(A1:A10,A1:A10&"")) If this post helps click Yes --------------- Jacob Skaria "learnlearn52" wrote: Perhaps this may have been answered before. I used formulas from the Discussion Groups but did not get the right answer. I have the following filtered data (alphanumeric) exactly: A100 A102 A104 A103 A108 A104 A102 A102 A103 A109 The total number of entries is 10 and the unique number of entries is 6.What formula should be used to calculate the unique number of entries? Thanks in advance |
Thread Tools | |
Display Modes | |
|
|