+ Reply to Thread
Results 1 to 7 of 7

Use VLOOKUP and then add the values from that row

  1. #1
    Registered User
    Join Date
    07-01-2013
    Location
    Bradford
    MS-Off Ver
    Excel 2010, Excel 2016
    Posts
    22

    Use VLOOKUP and then add the values from that row

    Hi

    I would like to add up the values from the row that matches the subject to put on another sheet. So for example if the subject is Drama I would like to find out the row and then add up from columns B to G.

    Capture.JPG

    Is this possible with a formula?

    Thanks
    Attached Files Attached Files
    Last edited by louiserace; 05-15-2017 at 05:05 AM. Reason: Solved

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,379

    Re: Use VLOOKUP and then add the values from that row

    You have only provided the source data. How do you want the data to look on the second sheet? Are you wanting the data to be copied there permanently, or are you creating some sort of interactive dashboard?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Use VLOOKUP and then add the values from that row

    If the search trerm, in this case - drama - is on the other sheet, cell A1:

    =SUMPRODUCT((Export_20170515_094446!$A$2:$A$6=A1)*Export_20170515_094446!$B$2:$G$6)
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    07-01-2013
    Location
    Bradford
    MS-Off Ver
    Excel 2010, Excel 2016
    Posts
    22

    Re: Use VLOOKUP and then add the values from that row

    The second sheet will have a list of subjects but will pull the values through so if I change the first sheet, the second sheet will change automatically. If the data was in one column I could do this easily with VLOOKUP but I don't know how to get round this problem of adding the appropriate row..

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Use VLOOKUP and then add the values from that row

    Incidentally, did you rally mean B to G and not B to J???
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-01-2013
    Location
    Bradford
    MS-Off Ver
    Excel 2010, Excel 2016
    Posts
    22

    Re: Use VLOOKUP and then add the values from that row

    Quote Originally Posted by Glenn Kennedy View Post
    If the search trerm, in this case - drama - is on the other sheet, cell A1:

    =SUMPRODUCT((Export_20170515_094446!$A$2:$A$6=A1)*Export_20170515_094446!$B$2:$G$6)
    Thanks Glenn. That works perfectly.

    By the way I did mean B to G since I need to know those with a grade better than 4.

    Thanks again

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,379

    Re: Use VLOOKUP and then add the values from that row

    Better than 4 would be B to F, not B to G.

+ 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. Replies: 13
    Last Post: 10-13-2014, 02:00 AM
  2. Replies: 1
    Last Post: 04-30-2014, 05:49 PM
  3. [SOLVED] With repeated values in a range, Vlookup only shows adjacent values for the last one
    By fsalina in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-15-2014, 03:19 PM
  4. [SOLVED] Vlookup is not working and giving =vlookup(B2,$T$2:$U$135,2,false) this kind of values.
    By yogeshsharma1981 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2013, 04:08 PM
  5. Replies: 3
    Last Post: 04-24-2013, 05:33 PM
  6. Replies: 1
    Last Post: 04-24-2013, 05:21 PM
  7. Replies: 2
    Last Post: 09-14-2010, 10:49 PM

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