Sum using probably 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/21/2004 5:03:00 PM
excel 39879 articles. 2 followers. Follow

3 Replies
373 Views

Similar Articles

[PageSpeed] 30

One way:

   =SUMIF(A:A,1,B:B)

In article <OQ#LYB6hEHA.356@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.
> 
> Thanks,
> 
> Alex
0
jemcgimpsey (6723)
8/21/2004 5:57:59 PM
Try this:

=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:OQ%23LYB6hEHA.356@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/21/2004 5:58:00 PM
=SUMIF(A1:A3,1,B1:B3)

-- 
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Alex Langlois" <alex.nospam@nospam.net> wrote in message
news:OQ%23LYB6hEHA.356@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
Andy
8/21/2004 5:58:42 PM
Reply:

Similar Artilces:

Credit Card Payments
I've been using MM for years now but just noticed something in MM 2005. If I go to reports and click on "Monthly Income and Expenses" the report doesn't list credit card payments even if I select "Include transfers..." in Customize Report. It seems that the report generator doesn't know about the special Transfer and Credit Card Payment categories. It only knows about the expense category "Credit Card Payments/Transfers". My question is does anyone actually use this expense category? It seems like it could cause some unfortunate side effect...

How can I use Outlook on both my laptop and desktop? I have cable.
and Netgear wireless version 4.0 Dear Bafflegab, have a look on this site, may it helps: http://www.slipstick.com/outlook/share.htm -- Oliver Vukovics Groupware for Outlook without Exchange: Public ShareFolder Notebook Synchronisation for Outlook: Public SyncTool New Domain: www.publicshareware.com "Bafflegab" <Bafflegab@discussions.microsoft.com> schrieb im Newsbeitrag news:59DE2C4E-ADC5-403C-AE3B-7DE1F9AF90C6@microsoft.com... > and Netgear wireless version 4.0 Thanks for the recommendation. It's a great site and one I never would have found on my own. I dow...

Exchange delegation using group doesn't work?
Hi, I am running Exch 2k3 SP1 in Windows SBS 2003. I tried to delegate Exch to user. What I did: I created a group called: Junior Admins I delegate at the Exchange top level the Junior Admins group to be Exch Full Admin. I assigned user: Sam to Junior Admins I use AdminPak.msi for Sam to control Exchange from her PC. Sam can get ESE on her PC (mmc), but can't open the server folder due the rights. I've also assigned Sam in the local administrator of that workstation. Things work if I assign Sam directly in the Delegation Control from ESE. How can I delegate control via group? ...

Using Matrix Items
Someone...want to tell me how or if I can default which item is first (selected) when a matrix item is scanned? Is there a way to set this up? -- Thank You Vince :) What are the dimensions you use? Rob "Vince" <Nagarra@yahoo.com.(donotspam)> wrote in message news:E001F1F0-3002-4E1B-8E75-8E62FC6EC01C@microsoft.com... > Someone...want to tell me how or if I can default which item is first > (selected) when a matrix item is scanned? Is there a way to set this up? > -- > Thank You > Vince :) Using Lot Matrix, what I have is a six pack of coke or a sin...

I have a problem with getting a total Sum of numbers (URGENT)
I'd really appreciate if someone could help me here. I am using Microsoft Excel 2002 on a Microsoft Office XP system. I a trying to develop a minor league baseball schedule, but am having som problems. I have attached a copy of my spreadsheet to give everyone some idea o what I'm doing here. I have where the fans list the "RUNS, HITS & ERRORS) of both teams an for each game of the series. I then have it set up in the TOTA WON/LOST column where if the T-Bones RUNS are more than the SALTDOG runs, a "1" appears in the WON column. If the SALTDOGS have a highe numbe...

making a decision tree using excel!
hello all, i`ve been trying to make a decision tree using excel, the problem i encounter is that i wish to put information in to b11 which is related to information in b3, the information in b3 is ether 0 or err and is based on a "y" or "n" being entered, does anyone know which formula to use which will print a true of false statement in b11 Hi Andrew, If you mean print "True" or "False" or your error b11: =Left(b3)="y" So you can use YES, Yes, yes, Y, y and get True everything else will show False Or to treat the error in ...

RE: Use this critical package for Internet Explorer
--hmrlmpxejw Content-Type: multipart/related; boundary="enmgzrgmzgzwwvlg"; type="multipart/alternative" --enmgzrgmzgzwwvlg Content-Type: multipart/alternative; boundary="hsqxuryiiedx" --hsqxuryiiedx Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 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 protect y...

using SetTimer
I use extensively the timers in my View mfc application but I find the documentation is not absolutely clear. I use the version with 3 paramteres - the last one is NULL which indicates using the View OnTimer routine. In the documentation it says to use KillTimer with the first parameter being used in the SetTimer function and in some other palce to use it with the return value of SetTimer. Can someone clarify that plaeas? Thanks Rick. "Rick" <rick@cortex.com> wrote in message -> I use extensively the timers in my View mfc application but I find the > documenta...

insert logo using AUTOCORRECT
Have been trying to insert my company's logo using AUTOCORRECT. It works like a charm in WORD. It doens't work in Excel. ex. when I type "lg" these characters should be replaced by my company's logo. Does anyone know how to do this in Excel? -- digicat ------------------------------------------------------------------------ digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14920 View this thread: http://www.excelforum.com/showthread.php?threadid=497273 ...

Using Excel to design business forms
I have been using excel hit and miss to design business forms. I have been pretty successful but in many places I have found myself doing many inelegant things to get stuff to layout. Is there a good place to start looking to see how this is done properly, as I have seen many well done forms produced in excel. ...

number in system using arrivals and departures
Is there and easy way to figure out the total number of persons in a system if have access to the arrival and departure times. I have tried to use counts with if statements, but I keep getting the number 1. I know this is not correct. Is there any special macros or functions Thanks Hi you may provide some more details about your data. Could you post some example rows (plain text - no attachments please) and describe your expecte result -- Regards Frank Kabel Frankfurt, Germany megv wrote: > Is there and easy way to figure out the total number of persons in a > system if have access...

Retreiving Xml child nodes using DataSet and XmlDataDocument
I am loading an Xml file from a sql server. I have one field that contains child nodes stored as text. The problem is, when I try to imort the dataset into a XmlDataDocument, it loads the whole thing as a text element. (with all of the special characters converted) The best solution I've found so far is to do a string replace on all of the special characters. Like so: DataSet ds = new DataSet(); ds.EnforceConstraints = false; SqlDataAdapter da = new SqlDataAdapter(query, connStr); da.Fill(ds, "Keywords"); XmlDataDocument xmlDoc = new XmlDataDocument(ds); string s = xmlDoc.S...

Creating a task with a duration = sum of all tasks durations
Is it possible to have a task that has a duration equal the sum of the duration for a set of tasks and that has work for that task calculated automatically based on a formula of say number of hours per day times the number of days duration? Duration is not additive but work is. Imagine a summary task that has 3 subtasks, each of which lasts one day. If the three are in parallel, starting together, they all finish at the same timne and all work on the summary is complete since all work on its children is complete, The summary task duration is 1 day. But as to work, each subt...

Creating timelines that don't use time as the horizontal measureme
Is it possible to use the timeline functionality of visio without using time as the horizontal meter? I want to have the same functionality of a timeline, but use it to express events that happen over a range of values from 0-100. ...

using picture on charts in exel 2002
After I updated to Exel 2002 the pictures I had displayed on a chart wall does not print properly... It looks fine on print preview but when you actually print it only show's a small portion of the picture... Before the update I had no problem... I have tryed every thing I know to make the correction.... Nothing has worked so far... Thanks The only thing that comes to mind is everyone's favorite whipping boy. Do your printer drivers need updating? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ jdishner...

A way to increase a number in a cell by using the + key
Hi, i do a lot of data entry and am looking for a way to increase the number in a cell (or range of cells) so that i dont have to enter the new number in each time. I have columns that separate items and i use the sheet to show the total count for each item in it's row. eg Item 1 Item 2 Item 3 2 5 4 I'd like to use the + key only to increase the counts for each item. Did i make sense?? It's a Friday and my mind isn't quite up for a lot of detailed explanations, lol. Any assistance would be appreciated. Tim That would require VBA How far are you will...

Cash withdrawal used a christmas money
Hello, I want to take some cash say 300.00 dollars and put 170.00 dollars away for Christmas and use the rest for misc expense. What is the best way to handle this in Money Plus? Thanks Frank Frank wrote: > I want to take some cash say 300.00 dollars and put 170.00 dollars > away for Christmas and use the rest for misc expense. What is the > best way to handle this in Money Plus? * Open a checking account at the bank. * Make a $300 withdrawal from your checking account. * Deposit $170 in the checking account; put $130 in your pocket. * Create a savings account in Money ...

Stem and Leaf Diagrams using Excel
Is there a way do do stem and leaf diagrams using excel and if so how do I do this. "Linda" <ljsinsc@aol.com> schrieb im Newsbeitrag news:009e01c39026$035decf0$a401280a@phx.gbl... > Is there a way do do stem and leaf diagrams using excel > and if so how do I do this. Hello Linda, I'm not familiar at all with this kind of Diagram, but if I had this homework to do, I would try at first, for a Data in A1, determine - the leave : using the function =RIGHT(A1,1) - the stem : through the function =LEFT(A1,LEN(A1)-1) in a second step I would sort together thes...

Sum of values in a form
Folks, I have read numerous threads on this topic, and understand that in order for my Sum() to work I need t ensure all references are made back to the Record Source. I have endeavoured to do this, but am still getting #Error. I am trying to Sum the revenue each tenant provides in a given date range, to give a total revenue figure. If the date range is a whole month, then the expression just takes the value of Rent PCM, and not Rent PCM * No. of Days, which would give some unusual results. My Expression looks like this: =Sum(Nz(IIf([Short Let]=-1,[Rent pcm],IIf(Day([Forms]...

formatting cell in vlookup
HELP I have an excel spreadsheet that is e:mailed to me by my home office. I use the vlookup feature to find the value in cell b from the information in a Proble At times when the answer is N/A and I go to the e:mailed spreadsheet to see if that number is really not there, it is in the e;mailed spreadsheet. If I type over that number in the cell on the e;mailed spreadsheet, then the formula will work in the target spreadsheet, sometimes This e;mailed spreadsheet is at a shared location on a servier, and the items I fix in the e;mailed spreadsheet on my desk top, are not available in any ...

Vlookup Problem!?
Hello I have a slight problem creating a vlookup, I've done a list of Hotels and beside that cell i want to create a Vlookup so that if its Hotel * then it displays �50, Hotel ** �70 Ive created the list but the problem is They all start with the same name which is 'Hotel' but with different number of stars, I'm guessing vlookup doesnt recognise the star character and just takes it into account that they are all the same, thus displays the same price. Thanks From John -- Neo1 ------------------------------------------------------------------------ Neo1's Profile: htt...

Using formulas for pivot table
I know you have the count field but is there away to input a formula? For example customer ordered 25 cases and each case weighs 4 lbs and the end result would need to be total pounds ordered. Thanks! If you do the calculation in the pivot table you may not get the result that you expect. If possible, add a field to the source data, and calculate the order total there. Then, add the OrderTotal field to the pivot table's data area. tskb wrote: > I know you have the count field but is there away to input a formula? > For example customer ordered 25 cases and each case weighs 4 lbs...

Using outlook express as newsreaedr from outlook
I have added the News command to the menu items of outlook, this is supposed to Open oulook express as the newsreader, however when I select news it opens a folder 1033 instead of Outlook express. How do I correct this problem? Thanks for any help Bob Answer has been posted countless times. To get the News command back, go back to the Programs tab in Control Panel | Internet Options and change the default newsreader to Outlook Express. If OE prompts you to make it your default newsreader the next time you launch it, clear the "Always perform this check..." box and then click the...

Using multiple databases
I have three seperate databases - all do a differnt function using a seperate database that holds all the data in tables (i.e. one uses queries to export, one to import, one for reporting only) - and I want to keep all of the databases seperate. Can I create another database that just has a form that works as a dashboard and connect to the other three to do functions. Essentially, I want to be able to select an option to open reports, import data, or export data and do it without opening the other databases seperatly (a function that each database will perform on it's own w...

Close Excel Completely Using VBA #3
Nice 1......E Oveson I tried to search for the correct function command...... I tried Application.Exit.......Not that Application.Close.......Not that And it turned out to be Application.Quit Thanks again Celtic_Avenge -- Celtic_Avenge ----------------------------------------------------------------------- Celtic_Avenger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1410 View this thread: http://www.excelforum.com/showthread.php?threadid=27391 ...