for your 2nd requirement the following loop will do the sum for you:

For each c in range("W7:W"&[w65536].End(xlUp).row)
if c.value= "ON" Then
s= s +c.offset.(-19,0)
end if
next


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=386389