Count Uniques EXCLUDING Some Entries
 

I have one column with both text and numeric entries. What formula can I use
to count the # of uniques, disregarding any cell that contains the word
"Unknown" or is blank? For example, in the following, I should get an answer
of 2 (which would be AB123 and GF999). The answer is probably something I
will be embarrassed that I couldn't figure out.....

Cell A1 Unknown
Cell A2 AB123
Cell A3
Cell A4 GF999
Cell A5 AB123
Cell A6 Unknown
Cell A7 AB123

Count Uniques EXCLUDING Some Entries
 

One way

=SUMPRODUCT(--(A1:A10<""),--(A1:A10<"Unknown"),1/COUNTIF(A1:A10,A1:A10&""))


--


Regards,


Peo Sjoblom


"Paige" wrote in message
...
I have one column with both text and numeric entries. What formula can I
use
to count the # of uniques, disregarding any cell that contains the word
"Unknown" or is blank? For example, in the following, I should get an
answer
of 2 (which would be AB123 and GF999). The answer is probably something I
will be embarrassed that I couldn't figure out.....

Cell A1 Unknown
Cell A2 AB123
Cell A3
Cell A4 GF999
Cell A5 AB123
Cell A6 Unknown
Cell A7 AB123

Count Uniques EXCLUDING Some Entries
 

Works like a charm! Thanks so much!!!

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A1:A10<""),--(A1:A10<"Unknown"),1/COUNTIF(A1:A10,A1:A10&""))


--


Regards,


Peo Sjoblom


"Paige" wrote in message
...
I have one column with both text and numeric entries. What formula can I
use
to count the # of uniques, disregarding any cell that contains the word
"Unknown" or is blank? For example, in the following, I should get an
answer
of 2 (which would be AB123 and GF999). The answer is probably something I
will be embarrassed that I couldn't figure out.....

Cell A1 Unknown
Cell A2 AB123
Cell A3
Cell A4 GF999
Cell A5 AB123
Cell A6 Unknown
Cell A7 AB123

Count Uniques EXCLUDING Some Entries
 

Thanks for the feedback


--


Regards,


Peo Sjoblom



"Paige" wrote in message
...
Works like a charm! Thanks so much!!!

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A1:A10<""),--(A1:A10<"Unknown"),1/COUNTIF(A1:A10,A1:A10&""))


--


Regards,


Peo Sjoblom


"Paige" wrote in message
...
I have one column with both text and numeric entries. What formula can
I
use
to count the # of uniques, disregarding any cell that contains the word
"Unknown" or is blank? For example, in the following, I should get an
answer
of 2 (which would be AB123 and GF999). The answer is probably
something I
will be embarrassed that I couldn't figure out.....

Cell A1 Unknown
Cell A2 AB123
Cell A3
Cell A4 GF999
Cell A5 AB123
Cell A6 Unknown
Cell A7 AB123