ExcelBanter - Need formula to count spinoffs

I have a sample of data I created and I'm trying to figure out how to
count the total number of direct and indirect referrals there are for
each member in a group. I have ...
Column A = User #
Column E = y/n (whether they paid for an upgrade)
Column F = Sponsor's User # (this will match a User # in Column A)
Column G = Total upgraded members personally sponsored)

For column G I figured out how to take criteria for two rows and count
them, then do an autofill of a formula for every row in column G. I
used ...
=SUMPRODUCT((E4:E103="y")*(F4:F103=A#)), where # was the row#.

This gave me the total upgraded members that were personally sponsored
by the person whose user # was associated with a row.

Now, I need column H and this is the tricky part. I need to count the
INDIRECT upgrades for each user. So if user 2 sponsored users
5,7,9,11,13,15 and user 5 sponsored users 6,8,10,12,14 and user 6
sponsored user 16,18,20,22 and user 16 sponsored users 17,19,21,23 then
what formula could I use to count the total number of upgraded members
in the chain started by User 2?

Along with that is another question: suppose I want one count for third
tier sponsorships and another count for second tier sponsorships and
another count for fourth, fifth, sixth, seventh, eighth, ninth, and
tenth tier sponsorships, and a final count for all indirect
sponsorships no matter how indirect?

Can this be done with a formula?

--
jamescarvin
------------------------------------------------------------------------
jamescarvin's Profile: http://www.excelforum.com/member.php...o&userid=36348
View this thread: http://www.excelforum.com/showthread...hreadid=561335