how to sum multiple matches to a vlookup command

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LoganTaylin - 20 May 2008 19:43 GMT

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 to this Message

Mike H - 20 May 2008 19:48 GMT

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

> I need to run a vlookup for an exact value in a range and for each match
> calculate the total of the cell adjacent.
[quoted text clipped - 18 lines]
> Alfred $324
> Pete $179

Reply to this Message

Max - 20 May 2008 19:48 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I need to run a vlookup for an exact value in a range and for each match
> calculate the total of the cell adjacent.
[quoted text clipped - 18 lines]
> Alfred $324
> Pete $179

Reply to this Message