+ Reply to Thread
Results 1 to 8 of 8

Sum values that correspond to

  1. #1
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Sum values that correspond to

    Hi there,

    I've been having some trouble with the following..

    I have something like:
    columns A B (sheet1)
    u 2
    v 1
    w 5
    x 3
    y 1
    z 2

    and in a seperate sheet:

    column D (sheet2)
    w
    z
    u

    I would like to get a sum of the values in sheet1!B:B that correspond to the values in sheet2!D:D (where only those in sheet1!A:A that match sheet2!D:D are sumed). In the above example I would like to get = 9.

    I had read that using something like the following would work but I just get the "#VALUE!" error...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does it matter that my actual sheet1!A:A and sheet2!D:D look more like "xyz, abc"?

    Thanks for your help,

    Geoff.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Sum values that correspond to

    enter this into E2 and fill down

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Sum values that correspond to

    Hi Mehmetcik,

    Thanks for your reply. I tried it but it's not quite what I'm looking for.

    I just need a formula in a single cell that sums the values in sheet1!B:B for which sheet1!A:A and sheet2!D:D are the same. In other words, I would like enter a formula in cell E2 (for example) that would result in the answer "9".

    I am making sense?

    Also I apologise for the title of this post (it was left incomplete accidentally)! I would have liked it to say "SUMIF + SUMPRODUCT? - Sum of B:B when A:A = D:D".

    Geoff.
    Last edited by Geoff.; 09-16-2014 at 05:01 PM.

  4. #4
    Registered User
    Join Date
    06-27-2013
    Location
    Boulder, CO
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: Sum values that correspond to

    I don't know why you're getting a #VALUE error. I copied your formula and it worked fine.
    /shrug

    -Z

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Sum values that correspond to

    Maybe this

    in E1 and copy down

    =SUMPRODUCT(($A$1:$A$6=D1)*($B$1:$B$6))

    A
    B
    C
    D
    E
    1
    u
    2
    w
    5
    2
    v
    1
    z
    2
    3
    w
    5
    u
    2
    4
    x
    3
    5
    y
    1
    6
    z
    2


    or for Total

    =SUMPRODUCT(($A$1:$A$6={"w","z","u"})*($B$1:$B$6))

    Or

    =SUM(SUMIF(A1:A6,D1,B1:B6),SUMIF(A1:A6,D2,B1:B6),SUMIF(A1:A6,D3,B1:B6))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  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: Sum values that correspond to

    Don't use the entire column D as the criteria reference. Use a smaller specific range like this:

    =SUMPRODUCT(SUMIF(Sheet1!A:A,Sheet2!D2:D5,Sheet1!B:B))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Sum values that correspond to

    Thanks Zodeeak, Alkey and Tony Valko (again .

    I tried it again as Tony Valko suggested and sure enough, it worked fine. I don't know why I didn't think of that!
    Zodeeak, did it work for you as I had done it or as Tony Valko suggested?

    Cheers everyone,

    Geoff.

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

    Re: Sum values that correspond to

    You're welcome. Thanks for the feedback!

+ 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: 2
    Last Post: 06-25-2012, 02:10 PM
  2. Replies: 6
    Last Post: 10-01-2009, 09:07 AM
  3. Making cell values correspond with Drop down List values
    By jrcream in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2008, 03:14 AM
  4. Correspond values of one row to another
    By wood929 in forum Excel General
    Replies: 1
    Last Post: 12-04-2005, 06:40 PM
  5. Replies: 0
    Last Post: 08-29-2005, 07:45 AM

Tags for this Thread

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