countif with cell ref vs fixed number...

[griff]

I am trying to use countif to establish whether a range of cells have a value
greater than or equal to a number in another cell - the number is 21.8% and
lives in cell C4

this formula works fine =COUNTIF(C16:R16,">=21.8%")

however if I try and make this work using the cell ref I get a result of
zero, eg

=COUNTIF(C16:R16,">=C4")

Please can someone tell me what I am doing wrong, or is this a limitation of
excel?

Many thanks in advance for your help,

griff

 

[Mike H]

Griff

Your formula is fine so it must be your data. Double check that the range
contains properly formatted percentages i.e.

clear a cell in the range format it as percentage and enter 22:00 in it
don't type the percentage symbol.

Mike

 

[Roger Govier]

Hi Griff

Because you have C4 within the quotes, Countif is looking for "C4" not the
contents of C4
Try
=COUNTIF(C16:R16,">="&C4)

 

[griff]

thanks Mike, but fyi I tried this already. since this was someone else's
sheet I did wonder if maybe there was a mixture of formats confusing excel.
to test the formula I created a new sheet with just basic numbers, no formats
and got exactly the same issue....

but thanks anyway for looking anyone else have any ideas?

cheers

griff

 

[griff]

Bingo!

Roger you are the man - thanks for your help!

cheers

griff

 

[Mike H]

That'll teach me to read a post more thoroughlyy, I tested
=COUNTIF(C16:R16,">=21.8%")
which of course works fine

Mike