Nested IF Statments

[markarivard]

I have a worksheet that is used to log phone calls. We can get several calls from the same person and I would like to identify the dup call. I am doing this with this statement:

=IF(COUNTIF(C$2:C$1000,C2)>1,"DUP","")

This works well to identify the duplicate callers. My challenge is that we also track closed calls with an "x" in column "N" titled "Closed". I would like the formula to ignore the closed calls and only alert me of duplate callers that are still open.

I have been looking at AND and OR statements but have yet to get a formula to work as desired.

Any help would be welcome.

Mark Rivard

 

[P_Guru]

Make column O a concatenation of columns C & N.

Then use

=IF(COUNTIF(O$2:O$1000,C2)>1,"DUP","")

 

[markarivard]

Thank you for the idea and it meets my reporting needs. Nice simple solution.