Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old May 20th 08, 07:48 PM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP and multiple matches tamz33 Excel Discussion (Misc queries) 2 August 14th 07 07:10 PM
Can vlookup be used to retrieve multiple matches [email protected] Excel Discussion (Misc queries) 4 August 11th 07 07:20 PM
Vlookup to return the sum of multiple matches AussieExcelUser Excel Discussion (Misc queries) 3 August 1st 06 12:29 AM
How do I get multiple matches when using the Vlookup function? Lisa F Excel Worksheet Functions 1 February 27th 06 11:17 PM
Multiple matches on VLOOKUP [email protected] Excel Worksheet Functions 2 May 9th 05 05:15 PM


All times are GMT +1. The time now is 01:41 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017