Formula for Concurrent Services

[Brawnystaff]

I am looking for a formula to identify concurrent services within a date range for a specific person and provider (different columns)

See attached spreadsheet with a "before" and "after" sheet (column "E" is where formula would go in "After" sheet). Basically, for each person in column C with visits with prov ID 55555, it identifies if there any other services by other providers (not 55555) for same person during same timeframe.

Any ideas? Assume some type of array formula. Thanks.

Test.xlsx

[FDibbins]

This will sort of give you what you want (It indicates YES in the correct "pair")...
=IF(COUNTIFS($C$2:$C$7,C2,$D$2:$D$7,"<>5555",$A$2:$A$7,">="&A2,$B$2:$B$7,"<="&B2)>1,"Yes","No")

I am working on returning blank spaces for you

[FDibbins]

deleted my duplicate post

[FlameRetired]

This is one way; this in E2 and filled down.

=IF(COUNTIF($C$2:$C2,C2)>1,"",IF(IFERROR(COUNTIFS($A2:$A$7,"<="&$A2,$B2:$B$7,">="&$B2,$D$2:$D$7,$D2),0),"Yes","No"))