countif (2003)

[jrose]

the formula
=COUNTIF(data!$Y:$Y,"<b2")
returns the wrong answer, 1, if the value in cell B2 is 18
the formula =COUNTIF(data!$Y:$Y,"<18")
returns the correct answer, 52 in my case.
What am I doing wrong, please?

[HansV]

You're counting cells whose value is less than the text string "b2", not cells whose value is less than that of cell B2.

=COUNTIF(data!$Y:$Y,"<"&B2)

[jrose]

Once again, magic, Hans!
I'd figured that there was someting iffy about my attempt at the formula, but Excel is often forgiving about cell references or static values.
Please, how did you know the correct syntax for the formula which works? Once again, Help doesn't.
Thanks again.

[HansV]

I've been using Excel for so long now (I started with Excel 1.0 for the Macintosh in 1985!) that I can't remember when I first learned about this... <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

[JohnBF]

And you're only 27 years old! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>