countif with cell ref vs fixed number...

  • Thread starter Thread starter griff
  • Start date Start date
G

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
 
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
 
Hi Griff

Because you have C4 within the quotes, Countif is looking for "C4" not the
contents of C4
Try
=COUNTIF(C16:R16,">="&C4)
 
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
 
That'll teach me to read a post more thoroughlyy, I tested
=COUNTIF(C16:R16,">=21.8%")
which of course works fine

Mike
 
Back
Top