Count Uniques EXCLUDING Some Entries

P

Paige

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
 
P

Peo Sjoblom

One way

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


--


Regards,


Peo Sjoblom
 
P

Paige

Works like a charm! Thanks so much!!!

Peo Sjoblom said:
One way

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


--


Regards,


Peo Sjoblom
Click to expand...
 

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

Count the remaining weekdays in a data range excluding holidays and start/end dates 5
Compare last digit in 5 cells for match 0
How to delete ALL cells containing the same data 0
Average Question 2
COUNT function 2
How to delete ALL cells containing the same data --- REVISED 0
How to delete ALL cells containing the same data 1
Dynamic pointing to a cell 0

Top