• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching, Sum and Lookup

V

VincentF

New Member
Hi All,

I need some assistance.

I'd like to match columns for clients names and then sum if multiple entries exist.

I.E. from the attached "Name" sheet I would like to match those clients with those in the "Data" sheet - specifically the client name column and then sum the "Base Ongoing Commission Amount" back to the clients name in the "Name" sheet.

Can someone please assist me?

Regards
Vince
 

Attachments

Hi Vince,

Have you tried using SUMIF in this case, try below formula in B2 and copy down:

=SUMIF(Data!$G$2:$G$10,Name!A2,Data!$AJ$2:$AJ$10)

Regards,
 
I tested that formula in the test sheet provided and it worked fine. It produced the following sums 264.1, 164.5, 127.4, 48.3, and 1099.

I suggest you check that you ranges correspond to the columns that you want to check and sum.
 
Hi Somendra,

Yes, you were correct it does work with that sample of data that I provided.

The actual data I have differs though and that is why I'm getting an error.

In the "data" sheet the client name (column G) does not always have the same details, it may only have the surname and or middle name included, so I require the match from the "name" sheet to be a partial match ie search for surname..

Can you assist?
 
Hi:

I cannot give you precise formula without seeing your data. But you understand that you can use wildcards in sumif function to get the results.
for eg:
=SUMIF(Data!$G$2:$G$10,A2&"*",Data!$AJ$2:$AJ$10)

Thanks
 
Hi,

I've re-attached the excel sheet with variations to the client names in the data file.

As you will find there are more than one variation for the client who's surname is Test and Cananan.
 

Attachments

Hi:

I have constructed 2 formulas one is normal formula using wild cards and the other one is an array formula ( execute pressing control+shift+enter keys), I did the array formula since I am not sure whether James and James peter is the same person. However, you have given only few data points I have tested based on these data points, you may have to tweak the formula in your original file.

Thanks
 

Attachments

Hi Vincent,

This seems to be fuzzy lookup and Excel in built formula can't do fuzzy lookup. You will need some addin which can do the same.

Regards,
 
You must log in or register to reply here.
Back
Top