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"))))

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.

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.

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))))

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.

=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.

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

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

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))))