vlookup question

  • Thread starter Thread starter sross002
  • Start date Start date
S

sross002

How would I look up a value in a list and return multiple corresponding
values and then add them up?

A B C
Paul 155 Pauk
Jake 34
Paul 145
Ann 222

=VLOOKUP(C1,$A$1:$B$4,2,TRUE))

Paul has two values that I would like to identify and add up.

Does anyone know of such a formula?

Thanks in advance.
 
Paul has two values that I would like to identify and add up.

Use SUMIF
In D1, copied down: =SUMIF(A:A,C1,B:B)
where the unique names are listed in C1 down
(watch out for the unique name typos!)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
 
Do I put that into the same cell that I have the VLOOKUP formula? How would
I squeeze it in wit that formula?
 
No, no, ditch the vlookup. Just use the SUMIF to get the totals for each
unique name. Thought that was the core issue?

You can use/apply autofilter on col A (insert a new header row first) to
easily retrieve the multiple line returns by name. Just choose the name from
the autofilter dropdown in A1.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
 
THANKS!

Max said:
No, no, ditch the vlookup. Just use the SUMIF to get the totals for each
unique name. Thought that was the core issue?

You can use/apply autofilter on col A (insert a new header row first) to
easily retrieve the multiple line returns by name. Just choose the name from
the autofilter dropdown in A1.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
Click to expand...
 
How would I look up a value in a list and return multiple corresponding
values and then add them up?

A B C
Paul 155 Pauk
Jake 34
Paul 145
Ann 222

=VLOOKUP(C1,$A$1:$B$4,2,TRUE))

Paul has two values that I would like to identify and add up.

Does anyone know of such a formula?

Thanks in advance.
Click to expand...

Another option would be to use a Pivot Table.

Have titles for the two columns. e.g. Name | Value

Then Insert/Pivot table.
Drag Name to Row labels
Drag Value to Data or Value area
If the Value does not SUM, right click and select field settings and
change it to SUM
Format and Rename columns as you prefer.

You can get a report like:

Value Totals
Ann 222
Jake 34
Paul 300
Grand Total 556
--ron
 
Back
Top