 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Create totals for close-matching names?
I want to insert a total in column C for names in column A that have the first several characters matching (I was thinking of using the LEFT function), but I only want the total to appear when the aggregate amount for a name is more than 100. Can anyone help me? A B C Contributor Name Amount Over 100? ABBOTT DOROTHY C 25 ABBOTT DOROTHY C 15 ABBOTT KATE 100 ABBOTT KATE J 50 ABDO MICHAEL 25 ABE KUMI 1400 ABELES KATHLEEN K 100 ABELL NANCY 10 ABELLERA EDDIE 25 ABELLERA ELLEN 25 ABELLERA ELLEN M 100 ABELLERA ELLEN M 200 ABELLERA ELLEN M 50 =IF(SUMPRODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)<100,"",SUMPRODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33))  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >I want to insert a total in column C for names in column A that have the > first several characters matching (I was thinking of using the LEFT [quoted text clipped - 19 lines] > ABELLERA ELLEN M 200 > ABELLERA ELLEN M 50 Thanks for your help, Don. Unfortunately I can't get that formula to work. I tried it, and changed your "G" and "H" columns to "A" and "B." In the sample data I posted, there are a few entries that should result in a >100 total appearing in column C, but the column remains blank when I paste your formula. Beyond that, I think there may be a larger problem. The formula you suggested seems to test for names with the first three characters "abd." My actual data table is thousands of rows, and contains names A through Z. Some names gave once, some twice, and some have given ten or more times. Basically, I just want to know who gave more than 100. What formula would total the contributions for close matches of a given name (say, matching the first 15 characters), wherever that name appears in the alphabet, and no matter how many time that name contributed? Let me know if I've messed up somehow, or if I misunderstood what you were trying to do... t_perkins > =IF(SUMPRODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)<100,"",SUMPRODUCT(--(LEFT($G$3:$G$33,3)="abd"),$H$3:$H$33)) > [quoted text clipped - 21 lines] > > ABELLERA ELLEN M 200 > > ABELLERA ELLEN M 50 I thought I gave you exactly what you asked for. " I want to insert a total in column C for names in column A that have the >> > first several characters matching (I was thinking of using the LEFT So, I gave you one for the first 3 characters "abd" for rows 3:33. Of course, you would need to modify to suit your needs. If all else fails send your workbook to the address below with COMPLETE details and examples of what you want. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Thanks for your help, Don. Unfortunately I can't get that formula to > work. [quoted text clipped - 48 lines] >> > ABELLERA ELLEN M 200 >> > ABELLERA ELLEN M 50 Thanks much Don, I may take you up on that if I don't have a breakthrough soon. One thing that confused me about your formula is that there were no names beginning with "abd" in my sample data that matched the specified criteria (i.e., having given more than 100) but there *were* names that began with "abb" or "abe." However, even when I tried entering those character strings in the formula, I still got no results. Can anyone else out there recommend another way to go about this? All suggestions are appreciated. > I thought I gave you exactly what you asked for. > " I want to insert a total in column C for names in column A that have the [quoted text clipped - 56 lines] > >> > ABELLERA ELLEN M 200 > >> > ABELLERA ELLEN M 50 >>ABDO MICHAEL 25 I DID test what I sent and got 190 for Abbott (Dorothy & Kate) and only 25 for abdo so NOT shown. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Thanks much Don, I may take you up on that if I don't have a breakthrough > soon. One thing that confused me about your formula is that there were no [quoted text clipped - 77 lines] >> >> > ABELLERA ELLEN M 200 >> >> > ABELLERA ELLEN M 50 Thanks, Don. I'll keep working on a solution that will allow me to total contributions from any and all names that are close matches. > >>ABDO MICHAEL 25 > I DID test what I sent and got 190 for Abbott (Dorothy & Kate) and only 25 [quoted text clipped - 81 lines] > >> >> > ABELLERA ELLEN M 200 > >> >> > ABELLERA ELLEN M 50 let us know you final result SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Thanks, Don. I'll keep working on a solution that will allow me to total > contributions from any and all names that are close matches. [quoted text clipped - 98 lines] >> >> >> > ABELLERA ELLEN M 200 >> >> >> > ABELLERA ELLEN M 50 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.