Share via

Highlight Triplets

Anonymous
2013-09-26T12:19:06+00:00

High I would like to highlight Triplets or above based on the information from 2 columns.

Can this be done using conditional formatting?

Only 20 Oak House would be highlighted. Does this make sense?

House number (Column C)   Address (Column D)

20                                           Oak House

20                                           Oak House

21                                           Oak House

22                                           Pallet towns

22                                          Pallet towns

22                                          Oak House

20                                          Oak House

20                                          Pallet towns

23                                          Pallet towns

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

Anonymous
2013-09-27T10:55:07+00:00

i am unable to re-create your error.  i got it all good.  did you select from C2:D2717?  the reason you are having this problem could be because you selected from C1

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

Answer accepted by question author

Anonymous
2013-09-26T12:46:52+00:00

hi there.  select the range you want to apply to (say from C2:D10).  in Excel 2007 & above, go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:

=COUNTIFS($C$2:$C$10,$C2,$D$2:$D$10,$D2)>=3

in Excel 2003, go to Format -> Conditional Formatting -> Formula is:

=SUMPRODUCT(($C$2:$C$10=$C2)*($D$2:$D$10=$D2))>=3

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

6 additional answers

Sort by: Most helpful
  1. Anonymous
    2013-09-26T13:21:19+00:00

    Hi there

    I have tried the formula but its not highlighting all 3 only 2 of them and then its highlighting a random house. Say they are three 18's it highlights two 18's and then highlights 17?

    Why is this?

    =COUNTIFS($C$2:$C$2717,$C2,$D$2:$D$2717,$D2)>=3

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2013-09-26T13:21:05+00:00

    Hi there

    I have tried the formula but its not highlighting all 3 only 2 of them and then its highlighting a random house. Say they are three 18's it highlights two 18's and then highlights 17?

    Why is this?

    =COUNTIFS($C$2:$C$2717,$C2,$D$2:$D$2717,$D2)>=3

    Was this answer helpful?

    0 comments No comments
  3. Anonymous
    2013-09-26T12:51:58+00:00

    Assuming your range is from A1:B10, row 1 being header.

    Select range A2:B10, while cursor is on A2

    Go to conditional formatting/New Rule/Use A formula/There give this formula

    =COUNTIFS($B$2:$B$10,$B2,$A$2:$A$10,$A2)>=3

    select formatting as per your choice/Apply

    Was this answer helpful?

    0 comments No comments