#### Sum using a Vlookup

Hi,
I'm looking for a way to make a sum of values in a column depending on
the respective value of another column.

Example:

A1 = 1
A2 = 2
A3 = 1

B1 = 100
B2 = 300
B3 = 125

I'd like to be able to make a sum of B values when A values = 1.  In that
case, my sum would be 225.

Thanks,

Alex

 0
8/31/2004 1:13:34 AM
excel 39879 articles. 2 followers.

9 Replies
526 Views

Similar Articles

[PageSpeed] 30

You can use SumIf.

=SUMIF(A1:A3,1,B1:B3)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Alex Langlois" <alex.nospam@nospam.net> wrote in message
news:%23CBbZdvjEHA.1644@tk2msftngp13.phx.gbl...
Hi,
I'm looking for a way to make a sum of values in a column depending on
the respective value of another column.

Example:

A1 = 1
A2 = 2
A3 = 1

B1 = 100
B2 = 300
B3 = 125

I'd like to be able to make a sum of B values when A values = 1.  In that
case, my sum would be 225.

Thanks,

Alex

 0
ragdyer1 (4060)
8/31/2004 2:01:35 AM
Why don't you use way the three responders to your exact same question
on August 21st gave you?

Instead of reposting a question, please look through the previous
find it in the archives:

Use your name in the Author box.

In article <#CBbZdvjEHA.1644@tk2msftngp13.phx.gbl>,
"Alex Langlois" <alex.nospam@nospam.net> wrote:

> Hi,
>     I'm looking for a way to make a sum of values in a column depending on
> the respective value of another column.
>
> Example:
>
> A1 = 1
> A2 = 2
> A3 = 1
>
> B1 = 100
> B2 = 300
> B3 = 125
>
> I'd like to be able to make a sum of B values when A values = 1.  In that
> case, my sum would be 225.
 0
jemcgimpsey (6723)
8/31/2004 2:17:35 AM
I'll be darned, I didn't even remember that!
You must be younger then me John.<g>
--

Regards,

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

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-1DBF9D.20173530082004@msnews.microsoft.com...
Why don't you use way the three responders to your exact same question
on August 21st gave you?

Instead of reposting a question, please look through the previous
find it in the archives:

Use your name in the Author box.

In article <#CBbZdvjEHA.1644@tk2msftngp13.phx.gbl>,
"Alex Langlois" <alex.nospam@nospam.net> wrote:

> Hi,
>     I'm looking for a way to make a sum of values in a column depending on
> the respective value of another column.
>
> Example:
>
> A1 = 1
> A2 = 2
> A3 = 1
>
> B1 = 100
> B2 = 300
> B3 = 125
>
> I'd like to be able to make a sum of B values when A values = 1.  In that
> case, my sum would be 225.

 0
ragdyer1 (4060)
8/31/2004 2:47:22 AM
In article <Oy7Y4VwjEHA.2948@TK2MSFTNGP11.phx.gbl>,
"RagDyer" <ragdyer@cutoutmsn.com> wrote:

> You must be younger than me

Yup - that's me, ol' babyface:

http://www.microsoft.com/communities/mvp/mvpdetails.mspx?Params=%7eCMTYDa
taSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22a81be233-4910-4acd-b367
-c4d0444726df%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParam
s%5e
 0
jemcgimpsey (6723)
8/31/2004 3:29:41 AM
For those that don't want to cut/paste 4 lines to get to see Rag's baby
face.

http://tinyurl.com/5pu53

--
Don Guillett
SalesAid Software
donaldb@281.com
"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-F1A207.21294130082004@msnews.microsoft.com...
> In article <Oy7Y4VwjEHA.2948@TK2MSFTNGP11.phx.gbl>,
>  "RagDyer" <ragdyer@cutoutmsn.com> wrote:
>
> > You must be younger than me
>
> Yup - that's me, ol' babyface:
>
> http://www.microsoft.com/communities/mvp/mvpdetails.mspx?Params=%7eCMTYDa
> taSvcParams%5e%7earg+Name%3d%22guid%22+Value%3d%22a81be233-4910-4acd-b367
> -c4d0444726df%22%2f%5e%7esParams%5e%7e%2fsParams%5e%7e%2fCMTYDataSvcParam
> s%5e

 0
Don
8/31/2004 11:39:35 AM
Or simply get a newsreader that knows how to deal with multi-line
URLs... <g>

In article <#8AJv80jEHA.596@TK2MSFTNGP11.phx.gbl>,
"Don Guillett" <donaldb@281.com> wrote:

> For those that don't want to cut/paste 4 lines...
 0
jemcgimpsey (6723)
8/31/2004 1:09:17 PM
I goofed. Picture is of you, JE.<G>
BTW. Don't most of us use oe?

--
Don Guillett
SalesAid Software
donaldb@281.com
"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-29CCF5.07091731082004@msnews.microsoft.com...
> Or simply get a newsreader that knows how to deal with multi-line
> URLs... <g>
>
> In article <#8AJv80jEHA.596@TK2MSFTNGP11.phx.gbl>,
>  "Don Guillett" <donaldb@281.com> wrote:
>
> > For those that don't want to cut/paste 4 lines...

 0
Don
8/31/2004 1:23:58 PM
In article <OdJ6D31jEHA.3944@tk2msftngp13.phx.gbl>,
"Don Guillett" <donaldb@281.com> wrote:

> BTW. Don't most of us use oe?

16.6% by posting. See

 0
jemcgimpsey (6723)
8/31/2004 1:57:07 PM
I didn't think I looked that young ... at least not in MY mirror!<g>

"Don Guillett" <donaldb@281.com> wrote in message
news:OdJ6D31jEHA.3944@tk2msftngp13.phx.gbl...
I goofed. Picture is of you, JE.<G>
BTW. Don't most of us use oe?

--
Don Guillett
SalesAid Software
donaldb@281.com
"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-29CCF5.07091731082004@msnews.microsoft.com...
> Or simply get a newsreader that knows how to deal with multi-line
> URLs... <g>
>
> In article <#8AJv80jEHA.596@TK2MSFTNGP11.phx.gbl>,
>  "Don Guillett" <donaldb@281.com> wrote:
>
> > For those that don't want to cut/paste 4 lines...

 0
ragdyer1 (4060)
8/31/2004 3:15:48 PM

Similar Artilces:

Use critical pack from MS Corp.
--ftrezuyirauxpxt Content-Type: multipart/related; boundary="claxayuktp"; type="multipart/alternative" --claxayuktp Content-Type: multipart/alternative; boundary="rhdvzclvudoovfbt" --rhdvzclvudoovfbt Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Partner this is the latest version of security update, the "September 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Instal...

What code would I use for this?
Let's say I have a bunch of Item Numbers in Col A. In Col B are quantities we have in stock. Is there some code I can use to total up each item group? For example let's say I have the following: col a | col b 1282-3622 | 10 1282-3611 | 12 1282-3582 | -5 9601-9000 | 8 9601-3672 | 14 ... when the code runs it would turn into this automatically col a | col b 1282-3622 | 10 1282-3611 | 12 1282-3582 | -5 17 9601-9000 | 8 9601-3672 | 14 22 ... it totalled up all the 1282's together, and all the 9601's together. Most of our item number a...

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

uninstall from command line using alternate credentials
WinXP SP3. Is there a way to do this? I am trying to find a way for a power user to uninstall a program using the msiexec + uninstall string from the registry using admin account credentials. Alternativly, I have tried to get the user to do a "run as" on the appwiz.cpl file but I get an error. UPDATE: also tried making a shortcut and running as admin but there is still an error with starting the msiexec. It says something like error statring program, it may be disabled or have no associated programs. I checked the windows installer service and it is running. ...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

can i use hotmail in office outlook? how? what email address i ca.
can i use office outlook2003 to download hotmail email? what email address does outlook2003 support? Hotmail access for new users requires that you use their paid service. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, snow asked: | can i use office outlook2003 to download hotmail email? what email | address does outlook2003 support? If you already own a free hotmail account and would li...

How can I use SY01400 for checking passwords?
I am currently working on a separate .NET app that allows users to edit some custom tables that i've created. When they first start this app, they are greeted with a simple dialog that asks for a company, plus their username and password. i would like to compare the password they enter to the encrtyped password in dynamics.sy01400, but I'm not sure what kind of encryption that table uses. is it a SQL based encryption, or does GP/Dynamics have their own? thanks! you cannot do that directly. However, if the user is logged in to GP, you can get the password using RetrieveGlobals.dll...

using insert with output
Hello there. I have two tables i need to insert table2 to table1. the field: ID (unique identity) on table2 exist already on table1. So i need insert the data to table1 and get new identity for each rows and get table of the new identity vs old identity: for examlple vstable old_iden, new_iden 5 , 12 6, 13 7, 14 I've tried to do it with output but it can't give me the old ID. is there a way to do this without doing it one by one? > I've tried to do it with output but it can't give me the old ID. is there a way to do this without doing it one ...

How to stop parts of a formula from being incremented when using the fill handle
I would like to know how a stop part of my formula from incrementin when using the fill handle. Here is the formula =VLOOKUP(F2,A2:B38,2,FALSE). I want the F2 cell to increment down which is currently happening, bu not the A2:B38 bit (this part is also incrementing but I don't want i to). I am assuming there will be a prefix or something i need to pu infront of the A2:B38 bit. Thanks in advance, haz1 -- haz1 ----------------------------------------------------------------------- haz13's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3464 View this thread: ...

Use that correction update from the Microsoft
--fobjkuxj Content-Type: multipart/related; boundary="mifxvowfcttgix"; type="multipart/alternative" --mifxvowfcttgix Content-Type: multipart/alternative; boundary="nwfkfltyzlsv" --nwfkfltyzlsv Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Partner this is the latest version of security update, the "September 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to help protec...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Excel File in Use Notification Missing??
We are using Windows Server 2000 & several versions of Excel (2000 / XP / 2003). When opening Excel files from the network, SOME users get the Notification that the File is In Use - locked for editing by <user> - with a choice to Open as Read-Only, Notify when avail, or Cancel. The pblm is that NOT EVERYBODY gets this msg - some simply have the file open up with the word (Sharing) in the Title Bar!! When you've got a deskful of work to input you're not always looking at the Title Bar to see if the file is Shared or not. I first thought this was caused by diff vers o...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

Prevent sequential number being used if record not saved
I have an invoice form that when opened, uses the following code to allocate the next invoice number (InvNo): Private Sub Form_BeforeInsert(Cancel As Integer) Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1 Me.Dirty = False End Sub Trouble is, if I then decide for some reason not to carry on, even if I click on the Close button rightaway without having entered anything anywhere, the invoice number is still used and appears in the Invoices table (without any values in any of the fields, of course). How can I prevent this? Ideally I would like to be able to...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

using intersection of ranges
Need to specify the intersection of two named ranges, but require either one or both of them to be referenced to other cells. In other words, someone would enter the range which is one or both of the two, and I need the formula to look at that cell and use its name in the intersection argument. Any ideas? I have tried TEXT, but that doesn't seem to do it. Suppose the rangenames are rangeA and rangeB, and the names are in D2 and D3 =INDIRECT(D2) INDIRECT(D3) Note the spaces between the two functions; space is the intersection operator. Now you'll have to test for one or two...

Cells stay highlighted, cannot use mouse
When I click on a cell in excel, that cell shows as clicked on, but when I move the mouse all surrounding cells become highlighted as well. So I cannot input anything anywhere, as moving the mouse around just keeps highlighting cells. And I cannot shut excel down as trying to move the mouse to a menu bar keeps highlighting cells in the worksheet. The only way to get out of excel is to End Task. I have come across this problem before, but I don't know how I inadvertently activate it, and to stop it was some combined of Ctrl-Enter but I can't remember what. Hopefully someone will un...

Multiple criteria VLOOKUP
Hi, I have a seven column dataset (A1:G2000) and I want to lookup a value based on 3 criteria: one for each of the 1st three columns, so where the value in column A = X, column B = Y, column C = Z, with XYZ being three criteria defined by three other cells. Can anyone point me in the right direction. I'm wanting to figure out how to do it without creating a helper column where columns A,B and C are concatenated, and using the concatenating field as a lookup key. Any help greatly appreciated, Thanks....Jason Array formula =3DINDEX(D1:D10,MATCH("x"&"y"&...

=SUM Ranges Do Not Update
I have a Excel 2000 spread sheet, with the following macro to insert new row. Sub InsertRow() ' ' Macro1 Macro ' Macro recorded 4/27/2004 ' 'GoTo label, MyString ActiveSheet.Unprotect Application.Goto Reference:="MyCell" ActiveCell.Select ActiveCell.EntireRow.Insert ActiveCell.Offset(-1, 0).Select ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAll Application.CutCopyMode = False ActiveCell.Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True End Sub The problem is that in the "Mycell&qu...

Can DoCmd.TransferDatabase be used to move table to BE
Hi O' Wonderous gurus, Can DoCmd.TransferDatabase move a table from the Front End to the Back End? I've tried the following syntax without success. A msgbox shows the strBackendPath to be correct. DoCmd.TransferDatabase acExport, "Microsoft Access", strBackendPath, acTable, "tblNewTemplate", strNewTbl I'm experimenting with a few things & if I make a small form that I can quickly import into any database I'm developing which has the ability to transfer tables from the front end to the back end & also delete tables then it will b...

use Pathfinder instead of finder in Office 2008?
I am using Word 12.1.2. on Mac OSX 10.5.5. I have installed Pathfinder as a replacement for Finder. However, when I "save" or "save as" in Office documents, the original apple Finder is invoked. Is there any way to get Office 2008 to use Pathfinder by default instead? newtomac wrote: > I am using Word 12.1.2. on Mac OSX 10.5.5. I have installed Pathfinder as a > replacement for Finder. However, when I "save" or "save as" in Office > documents, the original apple Finder is invoked. Is there any way to get > Office 2008 to use Pathfinde...

Using Macros to remove a name from a list
I own a small business with roughly 45 employees (mostly teenagers). There's lots of turnover. I've got a spreadsheet that I use to track tips according to the role and shift that our staff works. My manager has the ability to select the staff member who earned the tip from an in-cell drop down list (using DV). Here's my question. I want to make it really easy for my managers to add or delete staff members from the drop down list. I've created a separate worksheet with two sections: "add staff member" and "delete staff member". When they want to...

Using XmlRootAttribute for deserialization
I've seen this come up before, but in my case, things are a little more complex, and I'm having a tough time figuring out how to set an element name that works. I have a configuration file that is my serialized object (I'm using a customer deserializer to add some more XAML-like capabilities): <AppConfigObject> <RuntimeType:Kernel xmlns:RuntimeType="MyNamespace.DefaultKernel, Kernel"/> </AppConfigObject> public class AppConfigObject { [XmlElement] public Kernel {...} } Problem I have is deserializing the child node <Kernel> because it al...

Sum value between dates problem driving me mad!
Good Evening All, I am really struggling here, have tried, nested IF's including AND's but am a bit stumped. I am fairly familiar with Arrays, but cannot seem to combine all. I have a data table (as detailed below). (Hopefully, this should be 'pastable' into Excel - it works for me) I simply wish to calculate the expected totals per month. Any help & assistance with this would be most welcome. Cheers, Mathew Note, earliest Start is 01/04/03 Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 01/04/2003 23.00 01/04/2003 09/07/2003 23.00 ...