count rownumbers in loop

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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

Regards

Roger Govier
 
=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:
 
Hello Roger,
Your formula did't helped me.Please try your self.It's giving Circular
reference error and result '0' in C1.
 
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.
 
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:
 
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
 
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
 
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.
 
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
 
Back
Top