COUNTIF conditional formatting meeting two conditions (Excel 2003)

[bunny3]

I am trying to use conditional formatting in Excel 2003 to highlight the first instance of a certain value in column C where the corresponding cell in A is also a different, specific value.

Currently, I am using =COUNTIF($C$2:$C3,$C3)=1 in row 3 (and so on for the other rows) to highlight the first time a client's name appears.

[TABLE="width: 456"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Work Date[/TD]
[TD]Client[/TD]
[TD]Staff[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/5[/TD]
[TD]Thompson[/TD]
[TD]Anthony[/TD]
[TD="align: right"]12:45 PM[/TD]
[TD="align: right"]1:45 PM[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/5[/TD]
[TD]Thompson[/TD]
[TD]Tim[/TD]
[TD="align: right"]12:45 PM[/TD]
[TD="align: right"]1:45 PM[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/5[/TD]
[TD]Smith[/TD]
[TD]Tim[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1/5[/TD]
[TD]Smith[/TD]
[TD]Tim[/TD]
[TD="align: right"]10:30 AM[/TD]
[TD="align: right"]11:30 AM[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2/5[/TD]
[TD]Thompson[/TD]
[TD]Mark[/TD]
[TD="align: right"]10:30 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2/5[/TD]
[TD]Smith[/TD]
[TD]Tim[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[/TR]
</tbody>[/TABLE]

[Columns A and E omitted deliberately.]

The current formula highlights rows 2 and 4: the first time "Thompson" appears in column C is cell C2 and the first time "Smith" appears in column C is cell C4.

Now I would like to change the formula so that it highlights the first instance of the client's name for each work day (column B). The result should be that rows 2, 4, 5, 6, and 7 are highlighted.

I tried =AND(COUNTIF($B$2:$B3,$B3)=1,COUNTIF($C$2:$C3,$C3)=1), but that doesn't highlight row 7, because the date 2/5 has already appeared in row 6.

Can you come up with a formula that can achieve what I want? Please keep in mind I'm using Excel 2003.

I hope my question is clear. Thank you for your help.

 

[C Moore]

Did you mean 2, 3, 4, 6, & 7? 5 would be a later time for Tim Smith on the same date.

 

[bunny3]

Did you mean 2, 3, 4, 6, & 7? 5 would be a later time for Tim Smith on the same date.

Yes, you're right, thank you.

 

[C Moore]

I put the staff name and client name together, not paying attention. I'm guessing it was supposed to be 2, 4, 6, & 7 highlighted.

 

[C Moore]

Can you do a helper column? My answer is an array formula, which you can't put directly in as a conditional format. In the first cell of the helper column, =IF(SUM(($B$2:B2&$C$2:C2=B2&C2)*1)=1,1,"") ,array entered (Ctrl+Shift+Enter), and copy down. Then your conditional format would be based on the helper column showing a 1.

 

[bunny3]

Can you do a helper column? My answer is an array formula, which you can't put directly in as a conditional format. In the first cell of the helper column, =IF(SUM(($B$2:B2&$C$2:C2=B2&C2)*1)=1,1,"") ,array entered (Ctrl+Shift+Enter), and copy down. Then your conditional format would be based on the helper column showing a 1.

Thank you, C Moore. I added a helper column, as you suggested, and the array formula you gave works great.