# Conditional formatting custom formula not working in google sheets

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

I tried also:

"=COUNTIF(\$B1:\$B100,"month")>1"
=COUNTIF(\$B1:\$B100,"month")>1
="COUNTIF(\$B1:\$B100,"month")>1"

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
• 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

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:

=COUNTIF(\$B1:\$B100,"month")

(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