Results 1 to 7 of 7

Thread: COUNTIFS with some duplicates removed

  1. #1

    COUNTIFS with some duplicates removed



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    hoping you can assist.

    The logic I'm after is:
    return the value of 1 if the employee was employed in a particular month however an employee can only be counted once in a month i.e. not duplicates.

    If there is no actual end date then there should be a value in each month the employee is still employed, up until the expected end date.

    This is what I currently have as the formula however it is not accurate:

    =IFERROR(SUMPRODUCT(1/(COUNTIF($B$4:$B$3386,$B3&"")*(OR(COUNTIFS($C3,"<=31/07/2013",$E3,">=01/07/2013"),COUNTIFS($C3,"<=31/07/2013",$D3,">=01/07/2013<=$G4"),COUNTIFS($C3,"<=31/07/2013",$E3,"",$D3,">31/07/2013"))))),"")
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    Try in H3:

    =--(SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(H$2&":"&EOMONTH(H$2,0))),ROW(INDIRECT($E3&":"&IF($G3="",$F3,$G3))),0)))>=1)

    copied across and down the table


  3. #3
    Hi

    Thank you

  4. #4
    Hi
    Thank you for you suggestion. Although it identifies the month an employee works, it doesn't solve my problem of only counting employee's once per month.

    Cheers
    P

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    The results in the cells should be 1s or 0s. So it only counts the employee once per month. What results are you getting?

  6. #6
    For example: Adam started on the 1/07/13 an ended on the 1/7/13 (Row 3), he then started on the 2/7/13 and remained employed until December. My results show a 1 in H3 & H18 for July, where I only want Adam counted once for July.

    Hopefully I'm making sense.

    I really appreciate your input.

    Paul

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    Try, in H3:

    =--AND(COUNTIFS($C$2:$C2,$C3,H$2:H2,1)=0,SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(H$2&":"&EOMONTH(H$2,0))),ROW(INDIRECT($E3&":"&IF($G3="",$F3,$G3))),0)))>=1)

    copied across and down


Posting Permissions

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