+ Reply to Thread
Results 1 to 2 of 2

unique values & specific text query

  1. #1
    Registered User
    Join Date
    01-27-2010
    Location
    chicago
    MS-Off Ver
    Excel 2007
    Posts
    4

    unique values & specific text query

    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)
    Last edited by verbatim; 01-27-2010 at 06:54 PM.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: unique values & specific text query

    Hi verbatim
    Aliitle more about the lay out
    Column A has a name and Column B has who referred them?
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1