+ Reply to Thread
Results 1 to 6 of 6

Lookup two columns and sum their values in a new column in Excel

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Lookup two columns and sum their values in a new column in Excel

    I believe there is a formula out there to solve this. I have two products list in column A and D of sheet1. Each product in column A and D has a corresponding sales number (i.e. number sold per product).

    I need a formula to lookup both column A and D, check if the product in A exist in D, and then sum (or add) their sales values in column H of sheet 1. If the product in column A is not found in D, we still need to extract the product sales value in column A into H.

    Note: Column G contains the unique Products list.

    For example, if column A, cell A2 contains a product (Acai and the product sales value is 40) that is not found in column D products list, the sales value for “Acai” in cell H2 is 40.

    See attached Excel sample workbook for your convenience. The values in cloumn G and H is the desired outcome - driven by formula.

    Thanks in advance for your assistance.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lookup two columns and sum their values in a new column in Excel

    Try this, copied down...

    =VLOOKUP(G2,A2:B25,2,0)+IFERROR(VLOOKUP(G2,$D$2:$E$20,2,0),0)
    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

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Lookup two columns and sum their values in a new column in Excel

    Hi,

    in H2 copied down

    =SUMIF($A$2:$A$25,G2,$B$2:$B$25)+SUMIF($D$2:$D$20,G2,$E$2:$E$20)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Lookup two columns and sum their values in a new column in Excel

    FDibbins & Richard Buttrey: Thanks for the quick solution to the posted problem. The two suggested formulas both did the trick.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lookup two columns and sum their values in a new column in Excel

    Happy to help

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup two columns and sum their values in a new column in Excel

    Here's another one...

    Entered in H2 and copied down:

    =SUMIF(A$2:D$25,G2,B$2:E$25)

    I noticed that some of the cells in column B are formatted to display just the integer. For example, cell B8s true value is 41.6666666666667 but the cell displays 42. For that reason the results of some of the SUMIF formulas are different from the results you show as the result you expect. So, you'll have to format the formula cells to display just the integer.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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: 11
    Last Post: 07-08-2013, 11:47 AM
  2. Excel sum column with values derived from a lookup table
    By jeremygraham in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-26-2013, 11:14 PM
  3. fill a column with values from 2 columns in excel
    By shejintr in forum Excel General
    Replies: 5
    Last Post: 02-11-2013, 01:55 PM
  4. Replies: 5
    Last Post: 08-24-2012, 10:59 AM
  5. Lookup adjacent column to multiple lookup columns.
    By JAMES4228 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-13-2009, 03:19 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