I have two columns as follows
A B
Officer Reporting To
President
VP-1 President
VP-2 President
VP-3 President
AVP-1 VP-1
AVP-2 VP-1
AVP-3 VP-1
AVP-1 VP-2
AVP-2 VP-2
AVP-3 VP-2
AVP-1 VP-3
AVP-2 VP-3
AVP-3 VP-3

How can I insert a formula in "C:C" to check out the number of total
juniors, i.e.:
1. President has span over all the staff, column C should be reflecting
"12". Each Vice President (VP) has "3" sub-ordinates and the Assistant Vice
Presidents (AVPs) have "0" subordinates.

Please help me out at the earliest.

Thanx in advance

Hi,

For the preisdent you could use

=COUNTA(B2:B14)

For thother put this in C3 and drag down
=COUNTIF(B$2:B$14,"="&A3)

Mike

"FARAZ QURESHI" wrote:

> I have two columns as follows
> A B
> Officer Reporting To
> President
> VP-1 President
> VP-2 President
> VP-3 President
> AVP-1 VP-1
> AVP-2 VP-1
> AVP-3 VP-1
> AVP-1 VP-2
> AVP-2 VP-2
> AVP-3 VP-2
> AVP-1 VP-3
> AVP-2 VP-3
> AVP-3 VP-3
>
> How can I insert a formula in "C:C" to check out the number of total
> juniors, i.e.:
> 1. President has span over all the staff, column C should be reflecting
> "12". Each Vice President (VP) has "3" sub-ordinates and the Assistant Vice
> Presidents (AVPs) have "0" subordinates.
>
> Please help me out at the earliest.
>
> Thanx in advance

Sorry,

But Mike you don't understand. It's just a small example. What I mean is
that a single/same formula in "C" could count the total linked cells. Lets
take another example:

A B C
P - 17 (4 VPs, 6 AVPs, 6 Officers & 1 Asst.)
VP1 P 9 (3 AVPs, 5 Officers & 1 Asst.)
VP2 P 3 (2 AVPs & 1 Officer)
VP3 P 1 (1 AVP)
VP4 P 0 (No subordinate)
AVP1 VP1 3 (2 Officers & 1 Asst.)
AVP2 VP1 2 (2 Officers)
AVP3 VP1 1 (1 Officer)
AVP4 VP2 1 (1 Officer)
AVP5 VP2 0 (No subordinate)
AVP6 VP3 0 (No subordinate)
Officer1 AVP1 1 (1 Asst.)
Officer2 AVP1 0
Officer3 AVP2 0
Officer4 AVP2 0
Officer5 AVP3 0
Officer6 AVP4 0
Asst.1 Officer1 0




"Mike H" wrote:

> Hi,
>
> For the preisdent you could use
>
> =COUNTA(B2:B14)
>
> For thother put this in C3 and drag down
> =COUNTIF(B$2:B$14,"="&A3)
>
> Mike
>
> "FARAZ QURESHI" wrote:
>
> > I have two columns as follows
> > A B
> > Officer Reporting To
> > President
> > VP-1 President
> > VP-2 President
> > VP-3 President
> > AVP-1 VP-1
> > AVP-2 VP-1
> > AVP-3 VP-1
> > AVP-1 VP-2
> > AVP-2 VP-2
> > AVP-3 VP-2
> > AVP-1 VP-3
> > AVP-2 VP-3
> > AVP-3 VP-3
> >
> > How can I insert a formula in "C:C" to check out the number of total
> > juniors, i.e.:
> > 1. President has span over all the staff, column C should be reflecting
> > "12". Each Vice President (VP) has "3" sub-ordinates and the Assistant Vice
> > Presidents (AVPs) have "0" subordinates.
> >
> > Please help me out at the earliest.
> >
> > Thanx in advance