In the example that you give here you would use the formula

=countif(B1:b6,"<"&b7)

This will update to the correct cells wherever you paste it to.

Regards

Carl


Jim Palmer Wrote:
Hi Dave

Sorry, but I don't understand your suggestion, or perhaps my example
wasn't clear.

It might be easier if I showed it down rows rather than across
columns.

Location,Qty
A,5
B,2
C,1
D,6
E,1
F,3

Suppose the values above are in columns A and B, rows 1 to 6

The "order at" is 4 stored in B7

I want the formula to say countif(B1:b6,<b7) as opposed to "<4"

When I copy it to other columns I'd like to use relative addressing.

That is, countif(c1:c6<c7)

Thanks

Jim


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=571888