Scenario: My employees get a referral fee for every employee they refer. The amount increases when 3 of the individuals they refer each refer 3 people and this continues in a trickle down effect for 3 cycles.
In the example below Brad Pitt, James bond & Demi Moore have each referred 3 people. I am in need of a formula for column C & column D below.
Query: What formula [that I can just copy & paste down the column] do I put in c2 to figure out if brad pitt has referred at least 3 unique names in column A?
Then what formula [that I can just copy & paste down the column] do I put in d2 to tell me if at least of the 3 unique names that Brad Pitt referred have also referred 3 unique names in column A?
Finally, what formula [that I can just copy & paste down the column] do I put in e2 to tell me how many of Brad Pitt's that referred 3 people have referrals that have also referred 3 unique names in column A?
Any help offered would be greatly appreciated. Thanks in advancenew employee referred by referred 3 people # of referals who referred 3 people # of those who referred 3 people
james bond brad pitt yes 1 0
angelina jolie james bond no 0 0
bill gates brad pitt yes 0 0
donnie brasco no 0 0
justin timberlake james bond no 0 0
sigourney weaver brad pitt yes 0 0
demi moore will smith no 1 0
bruce willis demi moore yes 0 0
ashton kutcher demi moore yes 0 0
elvis presley james bond no 0 0
michael jordan demi moore yes 0 0
Peyton Manning no 0 c
HTML Code:
--------------------------------------------------------------------------------
subjects that are probably not the solution that i read on only to become more confused:
count the number of unique values occurring posted by specific user
count if referred by is c12[mike dee] - then see what dates are < d12 - range?
=COUNT(IF((Sheet6!I7:I10008=C12)*(Sheet6!L7:L10008<=D12),Sheet6!L7:L10008))
add up unique values [freq] - match=text - len=blank cells -
SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))
Check if a cell matches specific text
=IF(A2="Buchanan", TRUE, FALSE)
Count how often multiple number values occur by using functions
count if referred by is c12[mike jackson] - then see what dates are < d12 - range?
=COUNT(IF((c2:c8=C12)*(d2:d8<=d12),d2:d8))
=COUNT(IF((c2:c8=C12)*SUM(IF(FREQUENCY(b2:b10,b2:b10)>0,1)))))
Count the number of unique values by using functions
=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))
Count the number of unique values by using functions
add up unique values [freq] - match=text - len=blank cells -
SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))
=COUNT(IF((c2:c8=C12)*(d2:d8<=d12),d2:d8)
Bookmarks