This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
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
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
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.
Answer accepted by question author
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
Answer accepted by question author
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
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
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
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