+ Reply to Thread
Results 1 to 3 of 3

vlookup with countif

  1. #1
    Forum Contributor spinkung's Avatar
    Join Date
    10-27-2006
    Posts
    199

    vlookup with countif

    thanks telyn, very helpful...

    ________________

    Hi All,

    Not sure if this is even possible (without vba) but thought i'd give it a go. I'm trying to perform a complex countif with a vlookup? (i think)

    here's what i've got.....

    a supplier number (and some other info) on one sheet...

    SUP123
    SUP234
    SUP345 etc...

    on another sheet each supplier has has a tier rating based on region, so tier 1, tier 2, tier 3....

    Supplier..........London..........South..........North
    SUP123..........1..................1................3
    SUP234..........2..................1................2
    SUP345..........3..................3................2

    I want to insert 3 columns on sheet on (t1, t2, t3) which takes the supplier number then looks up the supplier on sheet 2 then counts the amount of tier 1's and if the count is greater than 1 then put a 1 in the cell. confused??

    This would mean if i did a count of t1 for SUP123 i'd get a result of 2 therefore putting the number 1 in the cell. If i did the same test for SUP345 then i'd get a result of 0 (because it never has t1) so put a 0 in the cell.

    This essentially is removing the regions and just letting me know if a supplier is a ever a t1, t2, t3. I've tried a few different mixes of countif with vlookup but with no joy.

    Anyone got any ideas??? I can send an example if this is not clear.

    Thanks, spin.

  2. #2
    Registered User
    Join Date
    12-22-2009
    Location
    Damietta, Egypt
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: vlookup with countif

    please can u uplad a sample sheet

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: vlookup with countif

    Possibly, this formula can be adapted to your sheet structure.

    =IF(COUNTIF(OFFSET(INDIRECT("Sheet2!"&ADDRESS(MATCH($A2,Sheet2!$A$2:$A$4,0)+1,1)),0,1,1,3),1)>1,1,0)
    Attached Files Attached Files
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

+ 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