Sum formular using vlookup
in [General]
|
Prev: menu bar
Next: INDEX/MATCH #REF! error
From: Wardy_1976 on 4 Jul 2008 16:00 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.
From: Mike H on 4 Jul 2008 16:11 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.
From: Ragdyer on 4 Jul 2008 16:15 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" <Wardy_1976(a)discussions.microsoft.com> wrote in message news:EBC713D3-02B0-45E1-847C-960623AADC21(a)microsoft.com... > 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.
From: Wardy_1976 on 4 Jul 2008 16:39 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.
From: Wardy_1976 on 4 Jul 2008 16:44
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" <Wardy_1976(a)discussions.microsoft.com> wrote in message > news:EBC713D3-02B0-45E1-847C-960623AADC21(a)microsoft.com... > > 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. > > |