I am needing help building conditional format formula for Google that meets the following criteria:

  • Column B email needs to appear more than 10 times AND
  • Column I data needs to be "healthy" (options are healthy/not-healthy)

The cell should ONLY turn the color if the email in Column B has appeared more than 10 times with the data in the corresponding I cell = healthy. I have tried variations of

=countifs(criteria1 range, etc [criteria 2 range, etc]) and it does not work.

1 Answer 1


I'm not certain if you're saying you need ten "healthy" entries and then they should all be highlighted, or you want at least ten entries and then all the healthy ones should be highlighted.

=and(countif($B$2:$B, "="&$B2)>=10, $I2="healthy")

This will flag any email that appears 10+ times, but only the healthy entries. countif() here will check how many times the current entry appears and as long as it sees enough, will check if that row is healthy with the and() before highlighting it.

=countifs($B$2:$B, "="&$B2, $I$2:$I, "=healthy")>=10

Since you mentioned countifs(), I assume this is what you were trying to do. This will check that there are at least ten rows that both have that specific email, and have the value of "healthy" in column I. It sounds like you were getting confused by the usage of [] in the help info for the countifs() function -- this is not something you should be including when you use the formula, rather it is the indicator google sheets uses to show you that those arguments are optional.

    – Louisa
    Commented Jul 11, 2022 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.