1. ## 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?

2. ## 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?

3. ## 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. ## 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>

5. ## 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. ## 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. ## 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.

8. ## 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)

9. ## Re: Match/Sumif (2003)

Thanks to all.

10. ## 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. ## 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. ## 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. ## 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. ## 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
•