Forum Discussion
EXCEL FORMULA HELP
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
=SUMPRODUCT(0+(B$2:B$11=D2),1/COUNTIFS(A$2:A$11,A$2:A$11&"",B$2:B$11,B$2:B$11&""))
=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))
Both of these will handle blank cells within the range A2:B11.
Regards
