I was waiting to see if you had success with Kostis' suggestion.

Thanks for the feedback!

Biff

"Bob Davison" wrote in message
...
Thanks guys-

I think I figured out my second question.

IF(B1="A",MAX(INDEX((C2:C$9<=C1)*(B2:B$9="B")*ROW( B2:B$9),,1))-ROW()+1,"")



Not an array.



Thanks for taking the time to help me.



Bob




Maybe this *array* formula?

=ROW()-MATCH(MAX(IF(($B$1:$B9="A")*($C$1:$C9<C9),$C$1:C9) ),IF($B
$1:$B9="A",$C$1:$C9),0)

This formula is attempts to work for employees of "B" and answer your
originally posted question. For employee 9 it returns 3, because the
latest-hired employee of "A", hired before E#9 is 3 rows above. Are
more senior members higher on the list? Is seniority based on
something else than hiredate?

If I change MAX to MIN it returns 6 for E#9 and 5 for E#8, which is
probably the difference of 1, if you count the present number or only
want the offset. In the latter case subtract 1 from the entire
formula.

HTH
Kostis Vezerides