ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   VLOOKUP (https://www.excelbanter.com/excel-discussion-misc-queries/238757-vlookup.html)

LMR29

VLOOKUP
 
I am trying to use a VLOOKUP to add multiple numbers with the same heading in
another sheet. For Example in Sheet 1 I have a name (Lemon) in sheet 2 I have
multiple $ amounts with the name Lemon. I want to add the total amounts for
all purchases made by lemon. I tried using the VLOOKUP but it only gives me
the first occurence of Lemon. How do I add up the totals for all of their
purchases? Thanks!

Dave Peterson

VLOOKUP
 
As long as you're looking for a total (or count), you could use =sumif() (or
=countif()).

=sumif(sheet1!a:a,"lemon",sheet1!b:b)

will sum the values in column B of sheet1 only if the corresponding value in
column A is Lemon.



LMR29 wrote:

I am trying to use a VLOOKUP to add multiple numbers with the same heading in
another sheet. For Example in Sheet 1 I have a name (Lemon) in sheet 2 I have
multiple $ amounts with the name Lemon. I want to add the total amounts for
all purchases made by lemon. I tried using the VLOOKUP but it only gives me
the first occurence of Lemon. How do I add up the totals for all of their
purchases? Thanks!


--

Dave Peterson

Luke M

VLOOKUP
 
Lets say in Sheet2 your names are in column A, values are in column B
=SUMIF(A:A,"Lemon",B:B)

Speculating references (on sheet 1, Lemon is in a2)...
=SUMIF(Sheet2!A:A,A2,Sheet2!B:B)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"LMR29" wrote:

I am trying to use a VLOOKUP to add multiple numbers with the same heading in
another sheet. For Example in Sheet 1 I have a name (Lemon) in sheet 2 I have
multiple $ amounts with the name Lemon. I want to add the total amounts for
all purchases made by lemon. I tried using the VLOOKUP but it only gives me
the first occurence of Lemon. How do I add up the totals for all of their
purchases? Thanks!


Jacob Skaria

VLOOKUP
 
Use the SUMIF() formula in Sheet1 cell B1...as below

Sheet1
ColA ColB
Lemon =SUMIF(Sheet2!A:A,A1,Sheet2!B:B)

Sheet2
ColA ColB
Lemon 3
lemon 1
Lemon 2
Apple 2
Apple 5

--
If this post helps click Yes
---------------
Jacob Skaria


"LMR29" wrote:

I am trying to use a VLOOKUP to add multiple numbers with the same heading in
another sheet. For Example in Sheet 1 I have a name (Lemon) in sheet 2 I have
multiple $ amounts with the name Lemon. I want to add the total amounts for
all purchases made by lemon. I tried using the VLOOKUP but it only gives me
the first occurence of Lemon. How do I add up the totals for all of their
purchases? Thanks!


francis

VLOOKUP
 
Hi

Assuming you have Lemon in A2 of Sheet1. In Sheet2 you have multiple
entries in col A2 down with Lemon among other names and $$ value in col B2
down. In B2 of Sheet1, place the below formula.
eg
Sheet 1,
A2 B2
Lemon =SUMIF(Sheet2!A1:A10,A2,Sheet2!B1:B10)

Sheet 2,
A2 B2
Lemon 10
Orange 5
Pear 3
Lemon 10
Orange 5
Pear 3
Lemon 10
Lemon 10
Orange 5

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"LMR29" wrote:

I am trying to use a VLOOKUP to add multiple numbers with the same heading in
another sheet. For Example in Sheet 1 I have a name (Lemon) in sheet 2 I have
multiple $ amounts with the name Lemon. I want to add the total amounts for
all purchases made by lemon. I tried using the VLOOKUP but it only gives me
the first occurence of Lemon. How do I add up the totals for all of their
purchases? Thanks!



All times are GMT +1. The time now is 12:19 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com