Sum of VLOOKUP

[Jerry B]

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

 

[Biff]

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

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.)

 

[Aladin Akyurek]

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.

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.)

 

[Ashish Mathur]

Hi,

Use the DSUM function. More info in MS Excel help

Regards,

Ashish Mathur