Sorry, the formula is right, but I mistyped the cell which it is to be put in
.. In cell C2 (and down) it really gives circular reference. The right answer
is cell D2 (and down)!

Regards,
Stefi


"TUNGANA KURMA RAJU" wrote:

Hi Stefi,
Your formula didn't work.Have you tried yourself ?.It fetched wrong
results..In your formula range $c$2:$c$7 given me circular reference error
and wrong data.Please go through my question thoroughly and study logic
behind.Let me explain the question again and its logic.
col a1:a6 has names JOHN,LARA,CARY,DAVID,LUCY,RAO
col b1:b6 has codes 4,6,1,0,6,4
these codes inturn indicates row numbers of their respetive bosses.So,toal
persons working under DAVID(row no.4)=
1.JOHN(code-4,row-1)----3.LARA(code-1,row-3)
2.RAO(code-4,row-6) ----4.CARY(code-6,row-2)
-----5.LUCY(code-6,row-5)

=5 persons
with the above logic at col c1:c6 the results should be like 1,0,0,5,0,2.
Could solve it?.Thanks.

"Stefi" wrote:

Put this in cell C2 and fill down as necessary!

=COUNTIF($B$2:$B$7;CELL("row";A2)-1)+SUMIF($B$2:$B$7;CELL("row";A2)-1;$C$2:$C$7)

Regards,
Stefi


€žTUNGANA KURMA RAJU€� ezt Ã*rta:

Hello Stefi,
Iwant formula result in column c only.your formula has range d2:d7 also.It's
not giving any result what I desire.Please go through my question carefully.

"Stefi" wrote:


=COUNTIF($C$2:$C$7;A2)+SUMIF($C$2:$C$7;A2;$D$2:$D$ 7)

and fill down!
Regards,
Stefi


€žTUNGANA KURMA RAJU€� ezt Ã*rta:

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.