Hello Roger,
Again you have not gone through my question thoroughly.Please refer my
question delicately.
column a contains names
column b contains 4,6,1,0,6,4(all represents row no of the respective bosses)
Notice my results I need 1,0,0,5,0,2
your formula is not giving the above results.
Thanks for reciprocating

"Roger Govier" wrote:

Hi
My apologies.
I did try it myself, but of course the formulae are entered in D1 and copied
down, and the data is in A1:C6

So Column A contains 1,2,3,4,5,6
Column B contains John, Cary, Lara, David, Lucy, Rao
Column C contains 4,6,1,0,6,4

If you put the formula in D1, then copy down you will see the result 1,0,0,2,0,2

Regards

Roger Govier


TUNGANA KURMA RAJU wrote:
Hello Roger,
Your formula did't helped me.Please try your self.It's giving Circular
reference error and result '0' in C1.

"Roger Govier" wrote:


Hi

With data in A1:B6, enter in C1
=SUMPRODUCT(--($C$1:$C$6=A1))
and copy down

Regards

Roger Govier


TUNGANA KURMA RAJU wrote:

column -------------- column ------------ column
A (Name) B(code #) C(total persons working
under him)
1 JOHN 4
2 CARY 6
3 LARA 1 = ?
4 DAVID 0 = ?
5 LUCY 6
6 RAO 4
Range B1:B6 contains code numbers(in text format) represent range A1:A6
(persons) immediate Boss's row number.JOHN's row number is 1 and his code no
is 4(B1),that idicates he has one subordinate(LARA) and one immediate
boss(DAVID), WHEREAS LARA has no subordinates.
total persons working under JOHN = 1
total persons working under CARY= 0
total persons working under LARA= 0
total persons working under DAVID=5
total persons working under LUCY= 0
total persons working under RAO = 2
What formula will work to get the above desired results in column c ?
What formula will get names of all persons under any person in range A1:a6?
Help me, I am trying to solve this since 2 months.