+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Vlookup Week and Countif

  1. #1
    Registered User
    Join Date
    04-12-2012
    Location
    MY
    MS-Off Ver
    Excel 2010
    Posts
    10

    Vlookup Week and Countif

    Hi All.

    I have a data set as below

    Q1 Q2
    WE1 4 4
    WE1 4 5
    WE1 3 4
    WE2 4 3
    WE2 3 2
    WE2 2 1
    WE3 1 4
    WE3 4 5
    WE3 5 4

    I need to come up with a weekly chart that shows percentage of scores for each week regardless of the question.
    that means , say for week 1, ill need to count how many scored 4, how many scored 5, etc. and then divide by total responses...
    ex. week 1 has 4 scores = 4 divide by 6 (total response)

    Hope i could get a head around what best formula to use to generate this.

    Thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,803

    Re: Vlookup Week and Countif

    with your data in A1:C10, use....

    =SUMPRODUCT((A2:A10="WE1")*(B2:C10=4))

    as an alternative, you add "WE?" and 1 etc in other cells and then reference them in the formula, instead of hard coding. that way, you can create a table that does all the cals for you...just be sure to check your table references when you copy, maybe absolute ($) them<
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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