unique values & specific text query

**verbatim** · 01-27-2010, 06:37 PM

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:

<b>Sheet11</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt;  padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px;  " /><col style="width:161px;" /><col style="width:148px;" /><col style="width:117px;" /><col style="width:243px;" /><col style="width:215px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >new employee</td><td >referred by</td><td >referred 3 people</td><td > * *# of referals who referred 3 people</td><td ># of those who referred 3 people</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >james bond</td><td >brad pitt</td><td >yes</td><td style="text-align:center; ">1</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >angelina jolie</td><td >james bond</td><td >no</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >bill gates</td><td >brad pitt</td><td >yes</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >donnie brasco</td><td > </td><td >no</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >justin timberlake</td><td >james bond</td><td >no</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >sigourney weaver</td><td >brad pitt</td><td >yes</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >demi moore</td><td >will smith</td><td >no</td><td style="text-align:center; ">1</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >bruce willis</td><td >demi moore</td><td >yes</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >ashton kutcher</td><td >demi moore</td><td >yes</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >elvis presley</td><td >james bond</td><td >no</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >michael jordan</td><td >demi moore</td><td >yes</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Peyton Manning</td><td >*</td><td >no</td><td style="text-align:center; ">0</td><td style="text-align:center; ">c</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IF(B2="","",IF<span style=' color:008000; '>(COUNTIF<span style=' color:#0000ff; '>($B$2:$B$13,B2)</span>>=3,"yes","no")</span>)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>

--------------------------------------------------------------------------------

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)