countif with cell ref vs fixed number...
 

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

countif with cell ref vs fixed number...
 

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

"griff" wrote:

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

countif with cell ref vs fixed number...
 

Hi Griff

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

--
Regards
Roger Govier

"griff" wrote in message
...
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

countif with cell ref vs fixed number...
 

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

"Mike H" wrote:

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

"griff" wrote:

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

countif with cell ref vs fixed number...
 

Bingo!

Roger you are the man - thanks for your help!

cheers

griff

"Roger Govier" wrote:

Hi Griff

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

--
Regards
Roger Govier

"griff" wrote in message
...
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

countif with cell ref vs fixed number...
 

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

Mike


"Roger Govier" wrote:

Hi Griff

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

--
Regards
Roger Govier

"griff" wrote in message
...
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