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 dupli...
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
Would I be able to do the same for the below based on location?
Name Hourly Location Total Number of hursh Hourly 1033 Hourly ? hursh Hourly 1033 Salaried ? peanut Hourly 1011 Kevin Hourly 1012 Alex Hourly 1013 Henr Hourly 1013 Alex Salaried 1012 peanut Hourly 1011 hursh Hourly 1013 Henr Hourly 1012
- Why? The set-up with COUNTIFS reciprocated by unity is much less efficient than the FREQUENCY/MATCH set-up, especially over large ranges.