Remember Me?

#1
May 20th 08, 07:43 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: May 2008 Posts: 1
how to sum multiple matches to a vlookup command

I need to run a vlookup for an exact value in a range and for each match
calculate the total of the cell adjacent.

For example, James appears in this list twice and I want to reach a sum of
his sales listed.

I know I can do a vlookup command, but I'm not sure what to do when there
are multiple matches. Thanks!

Name Sales Name Total Sales
James \$120 James
Bob \$168 Bob
Randy \$65 Randy
James \$54 Henry
Henry \$796 Pete
Pete \$164 Alfred
Bob \$638
Alfred \$945
Randy \$12
Henry \$578
Alfred \$324
Pete \$179

#2
May 20th 08, 07:48 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 11,501
how to sum multiple matches to a vlookup command

Try

=SUMPRODUCT((A2:A13="James")*(B2:B13))

you may also get away with
=SUMPRODUCT((\$A\$2:\$A\$13=A2)*(\$B\$2:\$B\$13))

and drag down.

Mike

"LoganTaylin" wrote:

I need to run a vlookup for an exact value in a range and for each match
calculate the total of the cell adjacent.

For example, James appears in this list twice and I want to reach a sum of
his sales listed.

I know I can do a vlookup command, but I'm not sure what to do when there
are multiple matches. Thanks!

Name Sales Name Total Sales
James \$120 James
Bob \$168 Bob
Randy \$65 Randy
James \$54 Henry
Henry \$796 Pete
Pete \$164 Alfred
Bob \$638
Alfred \$945
Randy \$12
Henry \$578
Alfred \$324
Pete \$179

#3
May 20th 08, 07:48 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,221
how to sum multiple matches to a vlookup command

You can get by with SUMIF
In E1, copied down: =SUMIF(A:A,D1,B:B)
where the unique names are listed in D1 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"LoganTaylin" wrote:
I need to run a vlookup for an exact value in a range and for each match
calculate the total of the cell adjacent.

For example, James appears in this list twice and I want to reach a sum of
his sales listed.

I know I can do a vlookup command, but I'm not sure what to do when there
are multiple matches. Thanks!

Name Sales Name Total Sales
James \$120 James
Bob \$168 Bob
Randy \$65 Randy
James \$54 Henry
Henry \$796 Pete
Pete \$164 Alfred
Bob \$638
Alfred \$945
Randy \$12
Henry \$578
Alfred \$324
Pete \$179

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post tamz33 Excel Discussion (Misc queries) 2 August 14th 07 07:10 PM [email protected] Excel Discussion (Misc queries) 4 August 11th 07 07:20 PM AussieExcelUser Excel Discussion (Misc queries) 3 August 1st 06 12:29 AM Lisa F Excel Worksheet Functions 1 February 28th 06 12:17 AM [email protected] Excel Worksheet Functions 2 May 9th 05 05:15 PM

All times are GMT +1. The time now is 08:14 AM.