Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Match/Sumif (2003)

    2 worksheets. On the first, Column A contains Surnames, and column B, hometowns. On the second, the same arrangemet (i. e., surnames in column A and hometowns in B, but there may very well be more surnames and hometowns on worksheet 2 as on worksheet 1), but in addition, in column C there is listed $$$ of donations. What I would like to do is match each surname and hometown, and then insert in worksheet 1 in column C the corresponding $$$ donation.
    Any ideas?
    Thanks in advance.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Match/Sumif (2003)

    I feel a cut down version of the workbook is in order here. I am struggling to work out why there is a second list on sheet 2, are all the names the sames as sheet 1 , just some duplicated ie Me making more than 1 donation?
    Jerry

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match/Sumif (2003)

    Hi Jezza,
    Good observation! This is a "hand-me-down" file I have been given to update and streamline, but not condense, per se. The reason there may be more surnames on worksheet two is that it is a combination of several, whereas worksheet 1 is a "build in progress" current version that is constantly updated and used for other purposes than worksheet 2. I thought it would be a simple multiple (i. e., 2 conditional) match problem (i. e., in order to insert the dollar donation in the respective cell in column c on worksheet 1, both of the contents of the cells in Columns A and B must match from both worksheets).
    Thanks.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Match/Sumif (2003)

    Eeeekk, had to think that one over for a bit....need another sherry <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Try Sumproduct in this format:

    =SUMPRODUCT((Sheet2!$A$2:$A$4=Sheet1!A2)*(Sheet2!$ B$2:$B$4=Sheet1!B2)*(Sheet2!$C$2:$C$4))

    I have attached a workbook for you to look at <img src=/S/drop.gif border=0 alt=drop width=23 height=23>
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match/Sumif (2003)

    Rudi,

    Your formula only looks for a match on the name column. If there were a Jerry in London and a Jerry in Cape Town, the SUMIF formula would add their donations together. Jezza's SUMPRODUCT formula takes both the name and town columns into account, so it distinguishes between the two.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match/Sumif (2003)

    If sheet 2 just contains more name/town combinations than sheet 1, not duplicates, as in Jerry's example, another possibility is the following array formula (confirm with Ctrl+Shift+Enter):

    =INDEX(Sheet2!$C$2:$C$4,MATCH(A2&"|"&B2,Sheet2!$A$ 2:$A$4&"|"&Sheet2!$B$2:$B$4,0))

    The MATCH function looks up the concatenated name and town and returns the index number of the row where it's found. INDEX then looks up the corresponding donation in column C.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Match/Sumif (2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> - You are right.

    I have modified the post in reference to show an example if you happen to be using Excel 2007.
    Regards,
    Rudi

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Match/Sumif (2003)

    <P ID="edit" class=small>(Edited by Rudi on 27-Dec-07 15:26. Tx Hans. Due to a flaw in my original reply I have modified it.)</P>Here is another version using Jerry's sample worksheet (and a sip of his sherry <img src=/S/yum.gif border=0 alt=yum width=15 height=15>).
    Modified...
    Please note that the formula below is only available for use in Excel 2007. The SUMIFS function is a new function in this version.
    You will need to change the references and also take note of the combination of relative and absolute references:
    =SUMIFS(Sheet2!$C$2:$C$4,Sheet2!$A$2:$A$4,Sheet1!A 2,Sheet2!$B$2:$B$4,Sheet1!B2)
    Regards,
    Rudi

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match/Sumif (2003)

    Thanks to all.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match/Sumif (2003)

    Hans,
    One final observation: would your formula work if there were 3 requested matches (say surname, hometown and gender)? If so, how would yout formula be phrased (assuming the age column is new column C on both worksheets, and the donation, column D. Also, will your formula work with 2003?
    Thanks again.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match/Sumif (2003)

    The formulas posted by Jezza and by me can easily be extended to work with more columns to match on. They will work in any version of Excel.
    The revised formula posted by Rudi can also be extended to match on more columns, but it'll work only in Excel 2007 (and future versions).

    The formula I posted would become

    =INDEX(Sheet2!$D$2:$D$4,MATCH(A2&"|"&B2&"|"&C2,She et2!$A$2:$A$4&"|"&Sheet2!$B$2:$B$4&"|"&Sheet2!$C$2 :$C$4,0))

    You must, of course, use the actual sheet name and the actual ranges you have.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match/Sumif (2003)

    Thanks, Hans. What if Sheet2 contains duplicate names, but with differing donation amounts (perhaps due to ther date they were received)?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match/Sumif (2003)

    If you want to add the amounts for duplicate names together, you can use Jezza's formula - it will add all amounts for rows that match on the first two (or three, whichever you specify) columns.

    If you don't want to add them, what do you want to do with them?

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Match/Sumif (2003)

    Hans,
    Jeeza's works great! Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •