+ Reply to Thread
Results 1 to 2 of 2

Combining a SUM function and a VLOOKUP Function

  1. #1
    Registered User
    Join Date
    02-13-2015
    Location
    London
    MS-Off Ver
    Office 2010
    Posts
    12

    Combining a SUM function and a VLOOKUP Function

    I am processing student exam results. In each row I have a student name followed by various letters/grades in the following columns (one column per subject). Elsewhere in the document I have a conversion table showing what each grade is worth in a separate points system (e.g. A = 52pts, B = 46 pts, etc). Some of the columns are blank as not all students do all subjects. See below

    E.g.
    Name Maths English Science Drama Total Points Average Points
    Joe A B A C ?? (e.g 52+46+52+40 = ??) ?? (e.g. average of 52,46,52,40)
    Adam B B D - ?? (e.g. 46+46+34 = ??) ?? (e.g. average of 46,46,34)

    Table:
    Grade Points
    A 52
    B 46
    C 40
    D 34

    What I need is a formula for the total points column which looks up each value from the row, works out the points score (by looking in the VLOOKUP table) and adds them together to form a total score. I know how to do it if I add an extra column for each Subject, and then add the scores separately but I'm sure there is a neater way of doing it in one column? I'd also like a similar thing with the average too. I think it may have something to do with an array formula but I've not used them before?

    Hope you can help!

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Combining a SUM function and a VLOOKUP Function

    Try this array formula entered with CTRL + SHIFT + ENTER

    =SUM(IF(B2:E2<>"",LOOKUP(B2:E2,$L$2:$M$5)))

    B2:E2 is the Grades for Joe
    L2:M5 is the table of Grades/Points

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Combining ROUNDUP function with IF function
    By VDENNIS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-18-2015, 02:48 PM
  2. Combining a IF function and lookup function
    By Trottlips in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-08-2014, 05:34 PM
  3. [SOLVED] Combining Text Function with Indirect Function
    By ninmjj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-31-2013, 10:34 AM
  4. combining an IF function with an INDEX array function?
    By Chris.eagle99 in forum Excel General
    Replies: 1
    Last Post: 03-01-2012, 04:43 AM
  5. combining --mid function with ifiserror function
    By kadi in forum Excel General
    Replies: 5
    Last Post: 06-27-2011, 04:40 PM
  6. Replies: 8
    Last Post: 08-23-2010, 05:28 PM
  7. Combining a VLOOKUP and PRODUCT function
    By andrewc in forum Excel General
    Replies: 4
    Last Post: 09-11-2009, 08:03 AM

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