+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Lookup Formula Help

  1. #1
    Registered User
    Join Date
    05-04-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Lookup Formula Help

    Hello all,

    I'm needing some help with building a formula to count the number of certain answers provided by each person. Here's an example:

    Name_____Answer
    Bob_______Yes
    Bob_______No
    Joe_______N/A
    Joe ______No
    Joe_______Yes
    Jamie_____Yes
    Josh______No
    Josh______No

    What I am needing to do is to have a forumula look up the name, then count the number of times they gave a certain answer. The resulting table would look something like this:

    Name_____Yes_____No_____N/A
    Bob________1_______1______ 0
    Joe________1_______1_______1
    Jamie______1_______0_______0
    Josh_______0_______2_______0

    The names in the original table may change, so I want it to look up the name, then count the number of times that name has a specific answer in the results table.

    Thanks for any help you can provide!

    ~~ Tim
    Last edited by Preistt; 05-05-2011 at 10:55 AM.

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Lookup Formula Help

    Hi,

    Have you considered using a pivot table?

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    05-04-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Lookup Formula Help

    I've attempted it, but with no luck. I should clarify the table i'm working to gather data from as it's a little more complex than the original example.

    There are actually 2 columns of names, and I need to get the number of each answer for each person. So I would think that the formula or something would need to find the names in columns Name A and Name B, then count the number of times an answer is given in the Answer Column.

    I've attached an example spreadsheet to help illustrate what i'm attempting to do.

    Thanks again!!

    ~~ Tim
    Attached Files Attached Files

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Lookup Formula Help

    Place this formula in B12 and drag across and down

    =COUNTIFS($A$2:$A$7,$A12,$C$2:$C$7,B$11)+COUNTIFS($B$2:$B$7,$A12,$C$2:$C$7,B$11)

  5. #5
    Registered User
    Join Date
    05-04-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Lookup Formula Help

    Awesome. That works!!! Thanks Cutter!

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Lookup Formula Help

    You're welcome. Don't forget to mark your thread as SOLVED (click on FAQ link at top of page for directions).

    And thanks for the "scales tap".

  7. #7
    Registered User
    Join Date
    05-04-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Lookup Formula Help

    Done. Thanks again!

+ 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