custom formula is: "=COUNTIF($B1:$B100,"month")>1."

I tried also:


I know it shouldn't work but I test all that comes to my mind.

I paste this in a cell: =COUNTIF($B1:$B100,"month")>1 and returns TRUE. The formula is working, the conditional formatting isn't.

Any ideas?

  • tried also the formula without quotation marks Commented Nov 6, 2019 at 14:19
  • 1
    excel or google sheets? which version? this should work - there is nothing wrong in your logic
    – Oren
    Commented Nov 6, 2019 at 18:14
  • Good point! it's google sheets, just updated the title to clarify it Commented Nov 7, 2019 at 14:56

2 Answers 2


In the end =COUNTIF($B1:$B100,"month")>1 worked, no quotation marks. I thougth I tried it before

  • I had the same issue even though the official help guide on this shows it with quotes around the whole thing, maybe just a formatting choice that causes confusion. Commented Jul 14, 2021 at 14:03
  • I used your formula in a column of checkboxes to highlight the header (B1) when all boxes were TRUE. There are 6 boxes in the column so >5 worked =countif(B2:B7,TRUE)>5 Before coming across your post I was using =and(B2,B3,B4,B5,B6,B7=TRUE) which was kind of messy and would be dreadful for a larger range of checkboxed in the column. Great solution. Thanks. Commented Dec 27, 2022 at 15:09

In your formula you have to omit the >1.
Your Conditional Formatting formula should be:


(The answer provided is in relation to your info. There is vital info missing from your question. I am not sure what exactly you try to achieve. Play around with the $ sign as well as the range and see your different results. Next time please do share a sample sheet. As always, as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well.)

  • conditional format responds to boolean only, =COUNTIF($B1:$B100,"month") returns a number, you MUST add >1 so the formula returns TRUE or FALSE. Commented Nov 8, 2019 at 16:36

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.