Results 1 to 14 of 14
Thread: Match/Sumif (2003)

20071227, 00:46 #1
 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.

20071227, 00:52 #2
 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

20071227, 00:58 #3
 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 "handmedown" 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.

20071227, 01:22 #4
 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

20071227, 14:59 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20071227, 15:02 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20071227, 15:17 #7
 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

20071227, 15:26 #8
 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 27Dec07 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

20071227, 16:00 #9
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Match/Sumif (2003)
Thanks to all.

20071227, 16:13 #10
 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.

20071227, 16:33 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20071227, 18:25 #12
 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)?

20071227, 18:32 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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?

20071228, 02:47 #14
 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.