Google Sheets: Apply conditional formatting to cell when two criteria match across different rows

Other Questions Google Sheets
1

I need assistance with setting up conditional formatting in Google Sheets. I’m looking to highlight a specific cell based on certain criteria being met in multiple rows.

Here’s what I’m working with: I have data in columns A, B, and C, and I want to highlight cell C1 under the following conditions:

  • The value in B1 exists elsewhere in column B (let’s refer to it as Bx)
  • The corresponding value in column A (let’s call it Ax) matches the value in A1

For instance, if B1 has “15:30” and A1 has “Project Alpha”, then I want cell C1 to be highlighted only if there’s another row where column B also shows “15:30” and column A contains “Project Alpha”.

Can I set this up using conditional formatting rules? What formula would help me check for these matching pairs across different rows?

2

yeah, that formula works but apply it to the right range. i had the same issue last week - forgot to exclude the header row and got weird results. if you’ve got empty cells, add another condition to filter them out: =AND(COUNTIFS(B:B,B1,A:A,A1)>1,B1<>"") so it won’t highlight blank B1 cells. saved me tons of headaches!

3

I’ve dealt with this exact problem before. The COUNTIFS solution works great, but don’t just apply it to C1 - you need to select the entire C column first. Then use =COUNTIFS($B:$B,$B1,$A:$A,$A1)>1 with those dollar signs for absolute referencing. Just heads up - the formula’s case-sensitive, so “Project Alpha” and “project alpha” won’t match. Found that out the hard way when my data had inconsistent capitalization.

4

This happens all the time with scheduling or project data. Here’s a simple fix using conditional formatting with COUNTIFS to check both conditions at once. Go to Format > Conditional formatting, pick your cell (C1), and choose “Custom formula is”. Then enter: =COUNTIFS(B:B,B1,A:A,A1)>1. This counts rows where column B matches B1 AND column A matches A1. If it finds more than one match, you’ve got duplicates and the formatting kicks in. I’ve used this tons of times to catch double-booked appointments in scheduling sheets. Just watch out for blank cells - they can mess up your counts.