Sum of VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to get the sum of all items tied to a specific alpha reference; e.g. total sales by salesman's name. For example:

Col A. Col B. Col C.
Name Address Sales
Fred xxxxxxx 25
John yyyyyyy 20
Bob zzzzzzz 23
John yyyyyyy 27

So, if I do a VLOOKUP for "John" I'd like to get a return answer of 47 in a single cell. (Note: Data entry is on an "as received" basis, so presorting is not an option.)

Thank you in advance.

Jerry
 
Hi Jerry!

Not sure what the VLOOKUP is doing but if you are using a
VLOOKUP on that sample data you may be getting incorrect
results as you have repeat values in col A. Unless you
specifically have the formula written in such a way, a
lookup will always return the first instance.

If you're simply wanting to sum Sales by Name, try either
of these:

A1 = Name or perhaps, your lookup formula that returns
Name.

=SUMPRODUCT(--(A3:A6=A1),C3:C6)
=SUMIF(A3:A6,A1,C3:C6)

Biff
-----Original Message-----
I'd like to get the sum of all items tied to a specific
Click to expand...
alpha reference; e.g. total sales by salesman's name. For
example:
Col A. Col B. Col C.
Name Address Sales
Fred xxxxxxx 25
John yyyyyyy 20
Bob zzzzzzz 23
John yyyyyyy 27

So, if I do a VLOOKUP for "John" I'd like to get a return
Click to expand...
answer of 47 in a single cell. (Note: Data entry is on
an "as received" basis, so presorting is not an option.)
 
Two options...

[1] Build a pivot table from your data.

[2] Create a distinct list of names in column E from E2 on (using Advanced
Filter or a formula system) which allows you to use a SumIf formula in F2 to
obtain the desired totals:

=SUMIF(A:A,E2,C:C)

where E2 houses the first distinct name of interest.

Jerry B said:
I'd like to get the sum of all items tied to a specific alpha reference;
Click to expand...
e.g. total sales by salesman's name. For example:
Col A. Col B. Col C.
Name Address Sales
Fred xxxxxxx 25
John yyyyyyy 20
Bob zzzzzzz 23
John yyyyyyy 27

So, if I do a VLOOKUP for "John" I'd like to get a return answer of 47 in
Click to expand...
a single cell. (Note: Data entry is on an "as received" basis, so
presorting is not an option.)
 
Back
Top