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
393 Views

Similar Articles

[PageSpeed] 13

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:

How do I use an IF statement to pick between two lists?
=IF(b6>0(first_list,second_list)) Doesn't work. I want to automatically use a list unless there's a value entered in the field. If there is, I want to be forced to use the second list. Use a list to do what? It seems to me that you need to incorporate this within a bigger formula. -- HTH RP (remove nothere from the email address if mailing direct) "Spatton17569" <Spatton17569@discussions.microsoft.com> wrote in message news:512BBBCF-AC58-47F2-8B83-7B575FA3128F@microsoft.com... > =IF(b6>0(first_list,second_list)) > > Doesn't work. I want t...

can't set rules/use Outlook bar after reinstall
New one to me... I just upgraded to a new machine (OS W2K Server w/ SP4), installed Office2K, and dropped in my existing pst file. Now I can't set rules (error: Operation Failed. Object could not be found). Also, when trying to use the Outlook Bar to switch to something other than Inbox, I get "Unable to display folder. Unable to display folder. Folder no longer exists." If I try to access anything (including (Inbox) from a shortcut I later created on the menu bar (via Tools>Customize), it works fine. Not having the rules is the big issue - any ideas? Thx, Chris ...

Sum Case Count on PO
I have modified my PO to show cases where necessary. Is there a way to put a summary count of all cases ordered at the bottom of the PO? ...

Page Footer sum Microsoft solution question
http://support.microsoft.com:80/kb/296249/en-us The above website gives a solution for sums per page but I cannot get any sums to work in the page footer. Are the instructions right? Has anyone else had problems with this? Michael wrote: >http://support.microsoft.com:80/kb/296249/en-us > >The above website gives a solution for sums per page but I cannot get any >sums to work in the page footer. > >Are the instructions right? Has anyone else had problems with this? The article is correct. Carefully, review it again and double check that you followed all of the s...

move cursor after inputting data without using enter key
I am using a scanner that reads barcodes and automatically enters data into a cell. The problem is that I'm not always close to the computer and want to have the cursor automatically go to the next cell after i scan one code without having to press enter after every scan. Is there a module or marco that can do this. This is not something that Excel could do. Even an event-driven macro couldn't be triggered until cell editting is completed (when ENTER is hit). However, you should be able to configure your barcode reader to automatically append an ENTER (or Carriage Return) to the ...

Log off from the CRM Application (log on using different user).
We were wondering if it is possible to log-off from the CRM Server application. When someone logs into the system, the user is somehow stored We have a pc on which the administrator logged into once. Now another user wants to log in on the server using that pc, but no user/password screen is displayed. We cleaned the temp. internet files, deleted the cookies and waited a long time so that the session (if one exists) is removed Could the problem be, that we enabled sessions on the application. This is off by standard, but we needed it for our custom plugin Regards Edwin Jongsma Accenture. H...

what office program do i use to make church bulletins
i am trying to make church bulletins for my computer but i dont know what program i need please help -- Tiff I would use MSWord. Microsoft shares lots of templates here: http://office.microsoft.com/en-us/templates/default.aspx Tiffy wrote: > > i am trying to make church bulletins for my computer but i dont know what > program i need please help > -- > Tiff -- Dave Peterson ...

sending BBC's using outlook express #2
I want to send a newsletter to members of my newsgroup but don't want to share their addresses with each other, so sending BBC's would seem to be the best option. But.... the system won't let me send more than 4. Is this an inbuilt restriction, or do I have a particular problem with the way my system is set up. If so, is there another way I achieve my aim? ...

Outlook used by other programs
I get a pop up box from Outlook that says another program is trying to access outlook, could be a virus. Do you want to allow the program to continue? I choose yes and nothing happens, pop up box reappears right away. You can check a box and let the program access the outlook up to ten minutes. Is there a setting to bypass this? Thanks. "Shane" <12blue@whtrans.com> wrote in news:0c7701c39ef9$0f1beec0 $a301280a@phx.gbl: > I get a pop up box from Outlook that says another program > is trying to access outlook, could be a virus. Do you > want to allow the p...

Multiple items when using CreateSalesOrder Web Services
All, I have a troule when using CreateSalesOrder with more than two items. The code below works only one item that I got from MSDN sample code. So, how can I add more items in this code to make one sales order with many items? static void Main(string[] args) { CompanyKey companyKey; Context context; SalesOrder salesOrder; SalesDocumentTypeKey salesOrderType; CustomerKey customerKey; BatchKey batchKey; SalesOrderLine salesOrderLine; ItemKey orderedItem; Quantity orderedAmoun...

sum() using indiect()
Can anyone post a formula that successfully uses SUM(INDIRECT("concatenated cell range")) where the path isn't local to the sheet it is on? ex: A1 = [TestBook.xls]Sheet1!B1 A2 = [TestBook.xls]Sheet1!B10 =SUM(INDIRECT(A1&":"&A2)) .....can you get that one to work?? See reply in .Functions Biff "Grymjack" <dcanham@rochester.rr.com> wrote in message news:nghng.50667$3B.13187@twister.nyroc.rr.com... > Can anyone post a formula that successfully uses > SUM(INDIRECT("concatenated cell range")) where the path isn't local t...

sum by skiping one cell
I want to sum only even cells from A1 : A1000 I used =a2+a4+a6+a8+…………..+a1000 Used this formula n cell b1 But now I want to solve this problem by some short formula Help me thank you See your post at worksheet.functions. Faisal Yameen wrote: > I want to sum only even cells from A1 : A1000 > I used =a2+a4+a6+a8+…………..+a1000 > Used this formula n cell b1 > But now I want to solve this problem by some short formula > Help me thank you > or even here -- HTH Bob Phillips "Aladin Akyurek" <akyurek@xs4all.nl> wrote in message news:41F3C09D.606080...

Online updates without using passport
Hello, Is there a way to use online updates without using passport? I'm not asking about file downloads - I want msmoney to connect directly to my newly opened citibank accounts and download account transactions whenever I select the on-line updates option. This works fine for the accounts I've set up in earlier versions, but using passport and publishing your account information to the web seems to be compulsary for any new accounts. I'm not comfortable with publishing all my financial information on the internet, regardless of how secure that might be, or regardless of wh...

Conditionally inserting blank rows using macro
I have a workbook containing the results from an archery competition, (the data in there is just to make sure that it is working fine, I will delete it once it is fully functioning), and I need some help with the code. I have found one problem when i come to format the team results page for presentational reasons. As was pointed out to me, there can be situations when there is not enough members to constitute a full team, (in fact we had this problem only Sunday.) A macro for generating the team results was given to me, but when there are less than four members in a team, it immediately move...

auto sum has gone off tool bar help
using excell, auto sum has dissapeared off tool bar how to get it back any help grateful Mowerman If you have no other customisations, right click on any toolbar, select customize and then select standard from the list of toolbars and click reset... -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS web: www.nickhodge.co.uk blog (non tech): www.nickhodge.co.uk/blog "mowerman" <mowerman@discussions.microsoft.com> wrote in message news:01A60B86-95DF-47AF-9D0F-5655AB2BDCF6@microsoft.com... > using excell, auto sum has ...

Why Use Project Management tools?
http://www.project-drive.net provides all tools, charts, reporting functions and a customizable dashboard for efficient project management and objective-oriented productivity Web based project management software allows you to save time and reduce costs at implementation and throughout the project lifecycles. Easy to learn and intuitive user interface even for non-technical business users I found Project Drive easy to use. It has made managing our research project a breeze. It has allowed my team members to work together, even from different locations. I just became part of t...

Criteria sum in report
I have a report looking something like this (just an example): Id Value 1 2 2 4 3 1 4 2 5 3 In the report footer I want a field that gives me the sum for values with id's between 2 and 4. Earlier I have solved this by making an extra query and a subreport, but I'm wondering if it could be solved otherwise. Maybee by using a sql sentence for the controllsource property on an ordinary textbox. Appreciate any suggestions -- regards hallgeir Try something like this in the Control Source of a text box in the Report Footer section: =Sum...

my font appears blurry when I use email merge
I am creating a doucment to be sent out for my business. After I use the email merge option the font apperas blurry when viewing through email. Is there any way I can sharpen that up? Thanks Sounds like you have converted the text to an image. Reference: Create and send e-mail publications by using Publisher 2007: http://office.microsoft.com/en-us/publisher/HA102200511033.aspx And this is a good one for the common formatting mistakes: Tips and troubleshooting for sending a publication page as an e-mail message (2003): http://office.microsoft.com/en-us/publisher/HP010390591033.aspx?pid=...

Use Julian Date To Create Serial Number
I am trying to find a way to create a unique serial number based upon the current julian date + an increment counter. I have sort of done this however the julian date function is not static and all the serial numbers change each day. I need to create be able to create the serial number (maybe by clicking on a cell) and then once the number populates the cell, it remains fixed. Any suggested would be welcome. Hi you have to use an event procedure for this. See: http://www.mcgimpsey.com/excel/timestamp.html -- Regards Frank Kabel Frankfurt, Germany "antho10359" <antho10359...

Using multiple shared Outlook 2002 calendars
I causally asked a co-worker if our department secretary scheduled meetings based on the combined Outlook 2002 calendars of all the members in our department. In other words, using a consolidated calendar to optimize the best time that all members can attend a meeting. The answer was a question: How do you do that? And I couldn't answer. But I was "positive" you could do that in Outlook using shared calendars, etc. So my question is: How do you consolidate multiple calendars of members of a department to isolate and maximize "open" time to schedule a department meetin...

StackedColumnChart for availability using macro
Hi, I am trying to create a chart in excel which will display the availability of certain equipment over time,the x axis will have time in the form of days, y axis will display availability of different equipment units in form of stackedcolumnchart displaying different colours for available,rented,quoted to customer.Is there any quick way to do this using a macro as the data will be available in excel or even otherwise manually without a macro? Regards -- excelprogrammer ------------------------------------------------------------------------ excelprogrammer's Profile: http://www.ex...

double line spacing using excel 2000
I have a paragraph of writing, how do i get the text to be double line spaced? I am using excel 2000. Excel doesn't have a line spacing setting. You could add line breaks by pressing Alt+Enter at the end of a line, then another Alt+Enter to create a blank line. Dan wrote: > I have a paragraph of writing, how do i get the text to be double line > spaced? I am using excel 2000. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Does this aply to newer versions of excel? "Debra Dalgleish" wrote: > Excel doesn't have a...

Cannot run net use command
When trying to run the 'net use' command I get a message like 'the command 'net' is not recognized as an internal or external command. The path is correct in Environment Variables - what could be causing this? "Mike" <texan767@hotmail.com> wrote in message news:a4d40d1d-c1eb-4ae9-b796-06d8fe849440@p35g2000yqh.googlegroups.com... > When trying to run the 'net use' command I get a message like 'the > command 'net' is not recognized as an internal or external command. > The path is correct in Environment Variables - what...

How to the determine the current subform using a macro
Hello, I have a frmTaskList form that contains 3 subforms using tab controls. Each tab has the following subforms: SubForm Name: Form Name: Task List Subform frmTask_List_Active_SubForm Active Task OverDue frmTask_List_Active_OverDue_SubForm New Task frmTask_List_Active_New_SubForm On the frmTaskList I have a button that opens a frmEdit form. When frmEdit opens I want it to contain the records of the one that was selected when the user hit the button to open the frmEdit. The issue is that there are 3 subforms, and I don't know ...

Using MS Office in XP & Windows Server 2008
Hi I have MS Office installed & running on my Windows XP PC. I have made this PC a dual boot system by installing Windows 2008 Server. Is it possible to use MS Office on Windows 2008 Sever without having to again install MS Office under Windows 2008 Server. Thanks no "MNJ" <MNJ@discussions.microsoft.com> wrote in message news:0109D1B8-8D4C-4654-BD36-540A66D70C15@microsoft.com... > Hi > > I have MS Office installed & running on my Windows XP PC. I have made this > PC a dual boot system by installing Windows 2008 Server. Is it possible...