conditional formatting - formula function - countif

[j4ymf]

Hello All

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

This one is fine

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

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

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

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

many thanks jason

 

[Snakehips]

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.

 

[Tetra201]

As an alternative, try this:

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

 

[Snakehips]

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?

 

[Tetra201]

@Snakehips:

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

 

[Tetra201]

This formula does work for CF:

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

 

[Snakehips]

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.

 

[Tetra201]

Surely the range refs need to be absolute...

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

 

[Snakehips]

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.��
Like the SUM construct. I rate your your posts.

 

[j4ymf]

Thank you for your help

Snakehips

this works great

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

ill get that bit complete then I be back
thanks for your help