I found the answer to my problem in this post:

http://www.microsoft.com/office/comm...=en-us&m=1&p=1

but I don't understand why this works when using a cell reference for
criteria. Does it work because the reference is text? If someone could
explain it to me I would appreciate it.

TIA

When you use a string like

"<D10"

Excel does not evaluate the value of D10 and incorporate it into the count criteria.

"<" & D10

will be evaluated to incorporate the value of D10...

HTH,
Bernie
MS Excel MVP


"AFJr" wrote in message
...
I found the answer to my problem in this post:

http://www.microsoft.com/office/comm...=en-us&m=1&p=1

but I don't understand why this works when using a cell reference for
criteria. Does it work because the reference is text? If someone could
explain it to me I would appreciate it.

TIA

=COUNTIF(B2:B6,"<D2")

will test for a text value which is less than the value of the text "D2" in
B2:B6

=COUNTIF(B2:B6,"<" & D2)

will test for value which is less than in that in cell D2.

D2 can contain data of various formats not just text.

So if D2=10 (numeric) , it will test for numbers < 10: the equivalent
formula is:

=COUNTIF(B2:B6,"<10")

HTH




"AFJr" wrote:

I found the answer to my problem in this post:

http://www.microsoft.com/office/comm...=en-us&m=1&p=1

but I don't understand why this works when using a cell reference for
criteria. Does it work because the reference is text? If someone could
explain it to me I would appreciate it.

TIA