# Need help getting a unique headcount with multiple conditions

1. ## Need help getting a unique headcount with multiple conditions

Need help finding the number of unique employee names that are both "in progress" and "complete" on the date 10/7.

G2 formula:
=IFERROR(ROWS(UNIQUE(FILTER(Table13[Employee], (Table13[Pass Name]=\$F2) * (Table13[Title Status]=\$H\$1)))), 0)

Formula.jpg

2. ## Re: Need help getting a unique headcount with multiple conditions

First, please read the yellow banner at the page. It's best always to attach a sample file rather than a picture (no one wants to re-type all your data).

This formula should give you the count of unique employees. It assume the DATE is in cell G1. Change this to wherever you are entering the date.

=COUNTA(UNIQUE(FILTER(Table13[Employee],(Table13[Complete Date]=G1)*(Table13[Title Status]="in progress")+(Table13[Title Status]="complete"))))

3. ## Re: Need help getting a unique headcount with multiple conditions

thanks for the quick response, the result should be 4. with the formula you gave it yielded 1. I'm linking the excel here in a few minutes.

4. ## Re: Need help getting a unique headcount with multiple conditions

Hey Greggb11,

i have attached the excel for you to work with. The formula you provided doesn't take into account the Pass Name either.

5. ## Re: Need help getting a unique headcount with multiple conditions

I was going by your request in Post #1, which says:

finding the number of unique employee names that are both "in progress" and "complete" on the date 10/7 (no mention of pass name).

That is what my solution gave you.

Why should G2 = 4? only 1 record has a date of 10/7. I thought you wanted a count where date=10/7.

BUT, if you don't care about the date, you can use this formula:

=COUNTA(UNIQUE(FILTER(Table1[Employee],((Table1[Title Status]="in progress")+(Table1[Title Status]="completed"))*(Table1[Pass Name]=F2))))
(this gives you 4)

If you care about the date, you can use this:
=COUNTA(UNIQUE(FILTER(Table1[Employee],(Table1[Complete Date]=F6)*((Table1[Title Status]="in progress")+(Table1[Title Status]="completed"))*(Table1[Pass Name]=F2))))

6. ## Re: Need help getting a unique headcount with multiple conditions

because the in progress are on going. i need to know how many people are on each project. They go through many of the same projects so there are multiple of the same names. In progress (no date)+ complete on date. sorry for the confusion.

7. ## Re: Need help getting a unique headcount with multiple conditions

=COUNTA(UNIQUE(FILTER(Table1[Employee],((Table1[Title Status]="in progress")+(Table1[Title Status]="completed"))*(Table1[Pass Name]=F2)))) worked, let me try on my larger document and see if it breaks.

8. ## Re: Need help getting a unique headcount with multiple conditions

when i put it in my main document it pickes up ALL completed. i need to have it only pick up the completed on a specific date and the in progress with no date

9. ## Re: Need help getting a unique headcount with multiple conditions

If you care about the date, you can use this:
=COUNTA(UNIQUE(FILTER(Table1[Employee],(Table1[Complete Date]=F6)*((Table1[Title Status]="in progress")+(Table1[Title Status]="completed"))*(Table1[Pass Name]=F2))))

this one worked. you're a life saver. this was driving me crazy

10. ## Re: Need help getting a unique headcount with multiple conditions

Based on your clarification, this one should NOT work because it will not include those that are in progress with no date.
=COUNTA(UNIQUE(FILTER(Table1[Employee],(Table1[Complete Date]=F6)*((Table1[Title Status]="in progress")+(Table1[Title Status]="completed"))*(Table1[Pass Name]=F2))))

To get the count for those that have been completed with a date and in progress without a date, I think this should work:

=COUNTA(UNIQUE(FILTER(Table1[Employee],(((Table1[Complete Date]=\$F\$6)*(Table1[Title Status]="completed"))+(Table1[Title Status]="in progress"))*(Table1[Pass Name]=F2))))

There are currently 1 users browsing this thread. (0 members and 1 guests)