Forum Discussion
EXCEL FORMULA HELP
Hello,
I am trying to count headcount but cant seem to count unique values.
example: Below is the text that i need a countif headcount and i cant seem to get unique values. If there are duplicates I only wan to have it count onces. How do I do that?
| Hursh | Serv | |
| Hursh | Serv | |
| Hursh | Serv | |
| Peanut | nj | |
| Henry | mo | |
| Anthony | mo | |
| Anthony | mo | |
| Anthony | mo | |
| David | Serv | |
| David | Serv |
15 Replies
=sumproduct(1/countif(a1:a10,a1:a10&""))
if you want to exclude blank cells
=sumproduct((a1:a10<>"")/countif(a1:a10,a1:a10&""))Hi hurshie ,
you can use the new Unique() function and wrap a CountA() around that.
=COUNTA(UNIQUE(B2:B11))
unfortunately, i do not have the Unique function tool as of yet. I guess my explination was not correct.
I need to find the total unique text and a count of location:
I know that there are 5 unique values but needs to break down like this.
Name Location Total Number of hursh Serv Serv ? hursh Serv NJ ? peanut nj MO ? Kevin nj Alex mo Henr mo Tom Serv peanut nj hursh Serv Henr mo Array formula (CTRL+SHIFT+ENTER) in F2:
=SUM(IF(FREQUENCY(IF(B$2:B$11=D2,MATCH(A$2:A$11,A$2:A$11,0)),ROW(A$2:A$11)-MIN(ROW(A$2:A$11))+1),1))
and copied down.
Regards
