# Thread: COUNTIFS with some duplicates removed

1. ## COUNTIFS with some duplicates removed

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

2. 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. Hi

Thank you

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

Paul

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