# conditional formatting - formula function - countif

#### j4ymf

##### Well-known Member
Hello All

I'm using the formula function in conditional formatting, How do I make this formula work

This one is fine

Code:
``=(COUNTIF(F14:F34, "H")>=5)``

I'm trying something like this but can't get it to work, i'm getting to many arguments

my plan is to turn all the cells black if greater that 5 = to H

Code:
``=(COUNTIF(F14:F34,F9,F10,F12 "H")>=5)``

many thanks jason

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Jason,

With the three ranges in F selected try the following custom formula...

=COUNTIF(\$F\$14:\$F\$34,"H")+COUNTIF(\$F\$9:\$F\$10,"H")+(\$F\$12="H")>=5

Hope that helps.

As an alternative, try this:

=SUM(COUNTIF(INDIRECT({"F14:F34","F9","F10","F12"}),"H"))>=5

As an alternative, try this:

=SUM(COUNTIF(INDIRECT({"F14:F34","F9","F10","F12"}),"H"))>=5

@Tetra201 I'd be surprised if that works for CF. Have you tried it?

@Snakehips:

Thank you for pointing this out -- I missed the CF part. It does not work for CF.

This formula does work for CF:

=SUM(--(F14:F34="H"),--(F9:F10="H"),--(F12="H"))>=5

This formula does work for CF:

=SUM(--(F14:F34="H"),--(F9:F10="H"),--(F12="H"))>=5

Surely the range refs need to be absolute....

=SUM(--(\$F\$14:\$F\$34="H"),--(\$F\$9:\$F\$10="H"),--(\$F\$12="H"))>=5

Edit : or at least the row refs.

Last edited:
Surely the range refs need to be absolute...
The OP did not have absolute rows/columns -- perhaps this is a single-cell CF.

The OP did not have absolute rows/columns -- perhaps this is a single-cell CF.

Point taken. OP's 'plan is to turn all cells black' when H count 5 or more lead me to believe that the cf would be applied to the whole of the test range, hence my comment. All will become clear in due course.&#55357;&#56847;

Snakehips

this works great
Code:
``=SUM(--(\$F\$14:\$F\$34="H"),--(\$F\$9:\$F\$10="H"),--(\$F\$12="H"))>=5``

ill get that bit complete then I be back

Replies
11
Views
1K
Replies
11
Views
2K
Replies
4
Views
251
Replies
9
Views
647
Replies
4
Views
3K

1,203,356
Messages
6,054,935
Members
444,759
Latest member
TeckTeck

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back