| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
count rownumbers in loop
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. |
|
#2
|
|||
|
|||
|
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. |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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. |
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
=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. |
|
#7
|
|||
|
|||
|
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. |
|
#8
|
|||
|
|||
|
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. |
|
#9
|
|||
|
|||
|
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. |
|
#10
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
| Count number to reach a cumulative value | Excel Worksheet Functions | |||
Hybrid Mode
