Good afternoon,
I am working with a dataset evaluating UPMH and I need to find a solution to quickly counting the number of unique employees who performed a function in a specific week. In order for the number to be correct, it should be defined based on four (4) criteria: Location, Activity Name 1, Activity Name 2, and Fiscal Week. I've spent the last two (2) days attempting to come up with my own solution using COUNTIFS, arrays, and COUNTA, but the closest I've come gives me the total count of all employees who performed that function according to the above criteria, without removing the duplicate values. I had four employees, but seven entries - I can't seem to find a solution to return the correct answer of four. I have dropped some partially-fictionalized data below as an example. In my real data set, I have 8 unique values for Location, 23 for Activity Name 1, 49 for Activity Name 2, and obviously 52 for Fiscal Week. The reason I need unique employee names is that among these 8 Locations I have 1592 unique employees who can all perform various functions any given week.
I'm hoping I'm just missing the simple solution here
I am working with a dataset evaluating UPMH and I need to find a solution to quickly counting the number of unique employees who performed a function in a specific week. In order for the number to be correct, it should be defined based on four (4) criteria: Location, Activity Name 1, Activity Name 2, and Fiscal Week. I've spent the last two (2) days attempting to come up with my own solution using COUNTIFS, arrays, and COUNTA, but the closest I've come gives me the total count of all employees who performed that function according to the above criteria, without removing the duplicate values. I had four employees, but seven entries - I can't seem to find a solution to return the correct answer of four. I have dropped some partially-fictionalized data below as an example. In my real data set, I have 8 unique values for Location, 23 for Activity Name 1, 49 for Activity Name 2, and obviously 52 for Fiscal Week. The reason I need unique employee names is that among these 8 Locations I have 1592 unique employees who can all perform various functions any given week.
I'm hoping I'm just missing the simple solution here
| Location | Activity Name 1 | Activity Name 2 | Fiscal Week | Employee |
| ELK | LUMPING | FLOOR LOADING | 1 | Joe |
| HOU | PICKING | FLOOR UNLOADING | 2 | John |
| REN | RECEIVING | FEDEX PICKING | 3 | Jill |
| SNB | SHIPPING | LTL PICKING | 4 | Jerry |
| WDF | TCAR | OE PICKING | 5 | Jill |
| WIL | GCS | TRUCKLOAD PICKING | 6 | Jerry |
| GVL | PLNA | PIVOT INBOUND | 7 | Jason |
| POR | TCAO | FEDEX SHIPPING | 8 | John |
| ELK | LUMPING | FLOOR LOADING | 1 | Joe |
| HOU | PICKING | FLOOR LOADING | 2 | John |
| REN | RECEIVING | FLOOR LOADING | 3 | Jill |
| SNB | SHIPPING | FLOOR LOADING | 4 | Jerry |
| WDF | TCAR | FLOOR LOADING | 5 | Jill |
| WIL | GCS | TRUCKLOAD PICKING | 6 | Jerry |
| GVL | PLNA | PIVOT INBOUND | 7 | Jason |
| POR | TCAO | FEDEX SHIPPING | 8 | John |
