Sum formular using vlookup
 

I am trying to create a formula that will sum up the values of a cell on
multiple lines.

i.e. The product name "Twix" appears on multiple lines in a worksheet, to
the right of the cell is a value that varies. I want a formula that looks
for all references of the word "Twix" in a worksheet and then sums up the
vales that correspond to the reference. There by giving me a total number
for all amounts of "Twix" on the worksheet.

I have been trying this now for 2 weeks and it is doing my head in! Please
help.

Sum formular using vlookup
 

Maybe

=SUMPRODUCT((A1:A25="Twix")*(B1:B25))

Mike

"Wardy_1976" wrote:

I am trying to create a formula that will sum up the values of a cell on
multiple lines.

i.e. The product name "Twix" appears on multiple lines in a worksheet, to
the right of the cell is a value that varies. I want a formula that looks
for all references of the word "Twix" in a worksheet and then sums up the
vales that correspond to the reference. There by giving me a total number
for all amounts of "Twix" on the worksheet.

I have been trying this now for 2 weeks and it is doing my head in! Please
help.

Sum formular using vlookup
 

Say the product names are in A1 to A100,
And the values are in B1 to B100.

Try this:

=Sumif(A1:A100,"Twix",B1:b100)

You could assign a specific cell to contain your criteria, so that you could
easily change it, without having to change the formula itself.

Say you enter Twix in C1, then:

=Sumif(A1:A100,C1,B1:b100)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Wardy_1976" wrote in message
...
I am trying to create a formula that will sum up the values of a cell on
multiple lines.

i.e. The product name "Twix" appears on multiple lines in a worksheet, to
the right of the cell is a value that varies. I want a formula that looks
for all references of the word "Twix" in a worksheet and then sums up the
vales that correspond to the reference. There by giving me a total number
for all amounts of "Twix" on the worksheet.

I have been trying this now for 2 weeks and it is doing my head in!
Please
help.

Sum formular using vlookup
 

Thank you, tried that but it did not work either. This is the worksheet I
have, as you can see we have twix xtra in 3 different drawers, I am trying to
sum up the total twix in tis cart. Whatever formula I use th answer is
always 5, which is the first reference in the table.
BAR TYPE : #B737 B Single Cart
STOWAGE : REAR Back Up 2 Cart 4

Drawer 1 Drawer 5
ORIGINAL PRINGLES OK 12 COCA COLA OK 8
TWIX XTRA OK 5 DIET COKE OK 8





Drawer 2 Drawer 6
ORIGINAL PRINGLES OK 12 BEER OK 20
TWIX XTRA OK 5






Drawer 3 Drawer 7
SOUR CREAM PRINGLES OK 12 CHICKEN AND VEGETABLE SOUP OK 14
TWIX XTRA OK 5 TOMATO AND VEGETABLE SOUP OK 7




Drawer 4
KETTLE CHIPS - CHEESE OK 2
KETTLE CHIPS - SEA SALT OK 2
J20 ORANGE & PASSIONFRUIT OK 4






TOTAL CART ITEMS 116


"Mike H" wrote:

Maybe

=SUMPRODUCT((A1:A25="Twix")*(B1:B25))

Mike

"Wardy_1976" wrote:

I am trying to create a formula that will sum up the values of a cell on
multiple lines.

i.e. The product name "Twix" appears on multiple lines in a worksheet, to
the right of the cell is a value that varies. I want a formula that looks
for all references of the word "Twix" in a worksheet and then sums up the
vales that correspond to the reference. There by giving me a total number
for all amounts of "Twix" on the worksheet.

I have been trying this now for 2 weeks and it is doing my head in! Please
help.

Sum formular using vlookup
 

Ragdyer, I cannot believe it was so simple.........I tried using sumif nested
in lookups etc and never worked. Your suggestion worked first time.

Thank you very very much.

"Ragdyer" wrote:

Say the product names are in A1 to A100,
And the values are in B1 to B100.

Try this:

=Sumif(A1:A100,"Twix",B1:b100)

You could assign a specific cell to contain your criteria, so that you could
easily change it, without having to change the formula itself.

Say you enter Twix in C1, then:

=Sumif(A1:A100,C1,B1:b100)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Wardy_1976" wrote in message
...
I am trying to create a formula that will sum up the values of a cell on
multiple lines.

i.e. The product name "Twix" appears on multiple lines in a worksheet, to
the right of the cell is a value that varies. I want a formula that looks
for all references of the word "Twix" in a worksheet and then sums up the
vales that correspond to the reference. There by giving me a total number
for all amounts of "Twix" on the worksheet.

I have been trying this now for 2 weeks and it is doing my head in!
Please
help.

Sum formular using vlookup
 

Hi,

I'm afraid your sample data didn't paste too well. If you'd like to post an
example of the workbook here and then post the link I'm sure someone will
help.

http://www.savefile.com/

Mike

"Wardy_1976" wrote:

Thank you, tried that but it did not work either. This is the worksheet I
have, as you can see we have twix xtra in 3 different drawers, I am trying to
sum up the total twix in tis cart. Whatever formula I use th answer is
always 5, which is the first reference in the table.
BAR TYPE : #B737 B Single Cart
STOWAGE : REAR Back Up 2 Cart 4

Drawer 1 Drawer 5
ORIGINAL PRINGLES OK 12 COCA COLA OK 8
TWIX XTRA OK 5 DIET COKE OK 8





Drawer 2 Drawer 6
ORIGINAL PRINGLES OK 12 BEER OK 20
TWIX XTRA OK 5






Drawer 3 Drawer 7
SOUR CREAM PRINGLES OK 12 CHICKEN AND VEGETABLE SOUP OK 14
TWIX XTRA OK 5 TOMATO AND VEGETABLE SOUP OK 7




Drawer 4
KETTLE CHIPS - CHEESE OK 2
KETTLE CHIPS - SEA SALT OK 2
J20 ORANGE & PASSIONFRUIT OK 4






TOTAL CART ITEMS 116


"Mike H" wrote:

Maybe

=SUMPRODUCT((A1:A25="Twix")*(B1:B25))

Mike

"Wardy_1976" wrote:

I am trying to create a formula that will sum up the values of a cell on
multiple lines.

i.e. The product name "Twix" appears on multiple lines in a worksheet, to
the right of the cell is a value that varies. I want a formula that looks
for all references of the word "Twix" in a worksheet and then sums up the
vales that correspond to the reference. There by giving me a total number
for all amounts of "Twix" on the worksheet.

I have been trying this now for 2 weeks and it is doing my head in! Please
help.

Sum formular using vlookup
 

Thanks for the feed-back ... AND ... a question.

My Sumif suggestion returns *exactly* the same values as Mike's Sumproduct
suggestion.

What happened when you tried his formula?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Wardy_1976" wrote in message
...
Ragdyer, I cannot believe it was so simple.........I tried using sumif
nested
in lookups etc and never worked. Your suggestion worked first time.

Thank you very very much.

"Ragdyer" wrote:

Say the product names are in A1 to A100,
And the values are in B1 to B100.

Try this:

=Sumif(A1:A100,"Twix",B1:b100)

You could assign a specific cell to contain your criteria, so that you
could
easily change it, without having to change the formula itself.

Say you enter Twix in C1, then:

=Sumif(A1:A100,C1,B1:b100)

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Wardy_1976" wrote in message
...
I am trying to create a formula that will sum up the values of a cell
on
multiple lines.

i.e. The product name "Twix" appears on multiple lines in a
worksheet, to
the right of the cell is a value that varies. I want a formula that
looks
for all references of the word "Twix" in a worksheet and then sums up
the
vales that correspond to the reference. There by giving me a total
number
for all amounts of "Twix" on the worksheet.

I have been trying this now for 2 weeks and it is doing my head in!
Please
help.

Sum formular using vlookup
 

A good question with much to commend it, I too am confused:)

"Ragdyer" wrote:

Thanks for the feed-back ... AND ... a question.

My Sumif suggestion returns *exactly* the same values as Mike's Sumproduct
suggestion.

What happened when you tried his formula?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Wardy_1976" wrote in message
...
Ragdyer, I cannot believe it was so simple.........I tried using sumif
nested
in lookups etc and never worked. Your suggestion worked first time.

Thank you very very much.

"Ragdyer" wrote:

Say the product names are in A1 to A100,
And the values are in B1 to B100.

Try this:

=Sumif(A1:A100,"Twix",B1:b100)

You could assign a specific cell to contain your criteria, so that you
could
easily change it, without having to change the formula itself.

Say you enter Twix in C1, then:

=Sumif(A1:A100,C1,B1:b100)

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Wardy_1976" wrote in message
...
I am trying to create a formula that will sum up the values of a cell
on
multiple lines.

i.e. The product name "Twix" appears on multiple lines in a
worksheet, to
the right of the cell is a value that varies. I want a formula that
looks
for all references of the word "Twix" in a worksheet and then sums up
the
vales that correspond to the reference. There by giving me a total
number
for all amounts of "Twix" on the worksheet.

I have been trying this now for 2 weeks and it is doing my head in!
Please
help.