COUNTING HIERARCHIAL LINKS (URGENT PLEASE)

[FARAZ QURESHI]

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

 

[Mike H]

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]

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