+ Reply to Thread
Results 1 to 10 of 10

Need help getting a unique headcount with multiple conditions

  1. #1
    Registered User
    Join Date
    11-09-2020
    Location
    Arizona
    MS-Off Ver
    excel online
    Posts
    7

    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
    Attached Files Attached Files
    Last edited by CaptainDizle; 11-10-2020 at 12:09 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,197

    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. #3
    Registered User
    Join Date
    11-09-2020
    Location
    Arizona
    MS-Off Ver
    excel online
    Posts
    7

    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. #4
    Registered User
    Join Date
    11-09-2020
    Location
    Arizona
    MS-Off Ver
    excel online
    Posts
    7

    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. #5
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,197

    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. #6
    Registered User
    Join Date
    11-09-2020
    Location
    Arizona
    MS-Off Ver
    excel online
    Posts
    7

    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. #7
    Registered User
    Join Date
    11-09-2020
    Location
    Arizona
    MS-Off Ver
    excel online
    Posts
    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. #8
    Registered User
    Join Date
    11-09-2020
    Location
    Arizona
    MS-Off Ver
    excel online
    Posts
    7

    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. #9
    Registered User
    Join Date
    11-09-2020
    Location
    Arizona
    MS-Off Ver
    excel online
    Posts
    7

    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. #10
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,197

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Headcount unique across 3 named ranges
    By junada0 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-29-2019, 04:23 AM
  2. [SOLVED] Get Unique List with multiple conditions
    By mamaexcel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-26-2018, 11:48 AM
  3. Count unique values with multiple conditions
    By liybpg in forum Excel General
    Replies: 6
    Last Post: 03-18-2015, 05:43 AM
  4. Replies: 6
    Last Post: 05-12-2014, 09:16 PM
  5. Replies: 1
    Last Post: 07-17-2013, 11:34 AM
  6. Excel 2007 : Headcount unique names
    By daniel_t in forum Excel General
    Replies: 2
    Last Post: 01-31-2011, 07:10 AM
  7. Count unique logs with multiple conditions of multiple sheets
    By Robert Bob in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-12-2007, 12:49 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1