& with COUNTIF

[AFJr]

I found the answer to my problem in this post:

http://www.microsoft.com/office/com...=en-us-excel&lang=en&cr=US&sloc=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

 

[Bernie Deitrick]

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

 

[Toppers]

=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