Can't Highlight Cells Appearing >=3x While Meeting Other Criteria

[KY Grouse Hunter]

I have a sheet with multiple columns that I track certain information for my club. I want to highlight cells in column B if those values in column B appear three or more times AND meet criteria in columns H and I.

I have attached my worksheet for reference.

For example, I would like to highlight cells or Dogs (column B) if they appear in that column (column B) three or more times AND meet the criteria of "Open" in column H and "Shooting Dog" in column I.

I am having so much trouble with something I feel is so easy.

Thanks in advance!

[PeteABC123]

I adjusted your column references to include those columns that contain the words OPEN, and SHOOTING DOG.

Try this in CF starting in B2 and copy down:

Please Login or Register  to view this content.

Format as desired. If it works for you, mark as solved.

Pete

[KY Grouse Hunter]

Thanks PeteABC123! That worked!

On another note, are you able to extract a unique list of those values based on the same criteria?

Thanks in advance!

[bebo021999]

List of >3 times appearance:
L2 then drag down

Please Login or Register  to view this content.

In case of list of unique dogs with "Shooting dogs" and "open", regardless time of appearance
K2:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[PeteABC123]

Thanks PeteABC123! That worked!

On another note, are you able to extract a unique list of those values based on the same criteria?

Thanks in advance!

Which values? (I'll try but it might be beyond my pay grade.)

Pete

[KY Grouse Hunter]

I am trying to extract a list of the unique dog names (column B) that have competed/appeared in 3 or more events meeting the "Open" restriction" (column F) and "Shooting Dog" classification (column G).

Thanks!

[JeteMc]

Hello KY Grouse Hunter and Welcome to Excel Forum.
This proposal employs a helper column (I) which may be moved and/or hidden for aesthetic purposes.
The helper column is populated using: =IF(AND(F2="Open",G2="Shooting Dog"),COUNTIFS(B$2:B2,B2,F$2:F2,"Open",G$2:G2,"Shooting Dog"),0)
The distinct list is populated using: =IFERROR(INDEX(B$2:B$158,AGGREGATE(15,6,(ROW(B$2:B$158)-ROW(B$1))/(I$2:I$158=3),ROWS(A$1:A1))),"")
Let us know if you have any questions.