Adding Another Condition to 'Countifs' Formula to not count duplicates

[tvanpelt]

Good Morning Everyone,

within this formula I'm using,

=COUNTIFS($D$2:D$199969, D2, $C$2:C$199969, "<=" & C2, $C$2:$C$199969, ">=" & EDATE(C2, -6), $H$2:$H$199969, "Stands")

The formula tells me how many compliance events a specific person has at the workplace on a rolling six month basis. This formula returns that number of compliance events in Column J. Each Compliance Event has a specific ID attached to it. What I need it to do is also check Column F and if its a duplicate id, instead of counting it as another compliance event, I just need it to return the number of events that is listed as if it werent counting duplicates.

I've tried indexing and matching but i keep getting it to return 0. I tried utilizing a help column but it kind of feels like it defeats the purpose. Is there something that I may do to get the results I need? I have attached an image with the example. Thank you

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IFERROR(ROWS(UNIQUE(FILTER($F$2:F$199969,($D$2:D$199969=D2)*($C$2:C$199969<=C2)*($C$2:$C$199969>=EDATE(C2, -6))*($H$2:$H$199969="Stands")))),0)

 

[tvanpelt]

Hi & welcome to MrExcel.
How about

=IFERROR(ROWS(UNIQUE(FILTER($F$2:F$199969,($D$2:D$199969=D2)*($C$2:C$199969<=C2)*($C$2:$C$199969>=EDATE(C2, -6))*($H$2:$H$199969="Stands")))),0)

Hi! Thank you so very much! That worked perfectly! I'm going to break the formula down to see what I was missing. Thank you

 

[Fluff]

You're welcome & thanks for the feedback.