COUNTIFS with some duplicates removed

P

Paul126

New member
Joined
Feb 19, 2015
Messages
4
Reaction score
0
Points
0
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"))))),"")
 

Attachments

  • CountIF with Dupilcates.xlsx
    8.8 KB · Views: 16
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
 
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
 
The results in the cells should be 1s or 0s. So it only counts the employee once per month. What results are you getting?
 
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
 
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
 
You must log in or register to reply here.
Back
Top