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.
|