Sum dependant on '1' value in another column

Hi,
Let's say I have a '1' or nothing in column 'L' and a time in seconds in 
column 'B'.  The '1' means the time is a legitimate one.  Can I do a sum of 
all the 'legitimate' times in column 'B' based on whether there is a 
corresponding '1' in column 'L'?  Is there a formula that will allow me to 
do that?  Sorry for the ignorance of the question but it is late and I am 
struggling to make this spreadsheet work for me.
Thanks in advance,
Dave 


0
kufgek (3)
1/22/2006 4:07:13 AM
excel 39879 articles. 2 followers. Follow

3 Replies
792 Views

Similar Articles

[PageSpeed] 1

Hi DL,

Look at the SUMIF  function in Excel help.

Try:

        =SUMIF(L1:L100,"=1",B1:B100)

Change the ranges to accord with your requirements.


---
Regards,
Norman



"DL" <kufgek@blweghiwe.com> wrote in message 
news:RxDAf.748400$xm3.648219@attbi_s21...
> Hi,
> Let's say I have a '1' or nothing in column 'L' and a time in seconds in 
> column 'B'.  The '1' means the time is a legitimate one.  Can I do a sum 
> of all the 'legitimate' times in column 'B' based on whether there is a 
> corresponding '1' in column 'L'?  Is there a formula that will allow me to 
> do that?  Sorry for the ignorance of the question but it is late and I am 
> struggling to make this spreadsheet work for me.
> Thanks in advance,
> Dave
> 


0
normanjones (1047)
1/22/2006 4:18:22 AM
Try this:
=SUMIF(L1:L100,1,B1:B100)

Adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"DL" wrote:

> Hi,
> Let's say I have a '1' or nothing in column 'L' and a time in seconds in 
> column 'B'.  The '1' means the time is a legitimate one.  Can I do a sum of 
> all the 'legitimate' times in column 'B' based on whether there is a 
> corresponding '1' in column 'L'?  Is there a formula that will allow me to 
> do that?  Sorry for the ignorance of the question but it is late and I am 
> struggling to make this spreadsheet work for me.
> Thanks in advance,
> Dave 
> 
> 
> 
0
1/22/2006 4:19:02 AM
You guys are wonderful!!  Thank you very much.  I have been beating my head 
against this one.  Again, thank you very much.
Dave

"Ron Coderre" <ronSKIPTHIScoderre@bigfoot.com> wrote in message 
news:4E907DDC-A3D6-42D2-BCF9-E9870F101D74@microsoft.com...
> Try this:
> =SUMIF(L1:L100,1,B1:B100)
>
> Adjust range references to suit your situation.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "DL" wrote:
>
>> Hi,
>> Let's say I have a '1' or nothing in column 'L' and a time in seconds in
>> column 'B'.  The '1' means the time is a legitimate one.  Can I do a sum 
>> of
>> all the 'legitimate' times in column 'B' based on whether there is a
>> corresponding '1' in column 'L'?  Is there a formula that will allow me 
>> to
>> do that?  Sorry for the ignorance of the question but it is late and I am
>> struggling to make this spreadsheet work for me.
>> Thanks in advance,
>> Dave
>>
>>
>> 


0
kufgek (3)
1/22/2006 1:17:13 PM
Reply:

Similar Artilces:

modify the Type of Field in a Table From another MDB through a Command button
Hello to all! I hope can help me. By technical questions that would be to me very long to explain, the following necessity arises to me: I need To modify the Type of Field in a Table From another MDB through a Command button. The field at issue would happen to be "Number" to "TEXT". Is this possible one? I wait for alternatives. Thanks in advance, and sorry for my poor english. Dreamer. - Hi Is it not possible to open the other database and simply open the table in design view and then change the field. It seems very complex to do the codeing what will most pro...

Cannot insert explicit value for identity column in table x
Sql Server 2005 9.00.4035. We have a hub and spoke topology which includes 6 tables with identity columns. I need to add a new subscription to a new server (same version/sp). I took backup from another subscriber and restored to new server, ran snapshot, push subscription. To make sure a new record would replicate, I added a record at the publisher with the followig text: Insert into tblicimagefolder (imagefolder) values ('dummy') A conflict occurs with the following: This failure can be caused by a constraint violation. Cannot insert explicit value for identity c...

add contents of one column
I have a budget that I want to automatically add the contents of one column as I'm working on the sheet. I am new to this and I need all of the helo that I can get. In B1: =SUM(A:A) -- HTH RP (remove nothere from the email address if mailing direct) "Pam" <Pam@discussions.microsoft.com> wrote in message news:FC5F8EE6-99D4-4327-B452-D34E441CA743@microsoft.com... > I have a budget that I want to automatically add the contents of one column > as I'm working on the sheet. I am new to this and I need all of the helo that > I can get. Assume your numbers ...

Looking Up Values
I have a database used to record results of laboratory tests, the tests are standard but the priced paid by the customer can vary. Currently I have a standard price included as a field in the Test Table but this is difficult when prices increase across the board as I have to close down the existing test and re-input it with the new cost, also I have to have a routine whereby a form is used to manually enter any alternative prices before a routine is run to invoice the customer. I would like to create some method whereby Access would look for the current price charged to that customer i...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

Sum Question
I am using Excel 2000 I have the following very simple formula in column j Sheet1 =SUM(B5*H5) I have this copied all the way down to line 40. It shows 0 in all of the cells all the way down. I would like for there to be a way that the cell would not show anything in it UNLESS there was something that it was calculating. For example. I have entered data in column b and column h through line 10, but it still shows 0 in column J all the way down to line 40. I realize that it is showing these 0's b/c I have placed the formula there, but is there a way that it will still calculate but only ...

Making cards --2 to a 8 1/2 x 11 page
I want to make cards to send out for memorials, etc. I want 2 cards to a page, top folded 5 1/2 x 8 1/2. What happened to the old Picture It! Publisher 2001? Do you want to make these cards in Publisher? If you do, look at page setup, folded card, tent card, width 4.25, height 5.5. Publisher will say it will print two pages per sheet -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "A Holt" <A Holt@discussions.microsoft.com> wrote in message news:F5F75EEF-B186-4960-9AEB-AF1C678492B7@microsoft.com... >I want t...

Highlight Cells with different values
Hi, I have a column that I want to locate the differences. I can use the F5---Special---Column Differences. But I wanted to know if I can do it with Conditional Formatting. Like if Value in A1 is different than the values in Column A:A, then it is highlighted... Any help appreciated Fuad Sounds like an Excel question..try one of the Excel newsgroups, this one is for Publisher. "Fuad" <Fuad@discussions.microsoft.com> wrote in message news:0536FB95-362E-412F-BD1C-99BF9B10E8CD@microsoft.com... | Hi, | | I have a column that I want to locate the differences. I can use th...

POP should allow blanket PO's with multiple items from 1 vendor
Clients have requested this multiple times and it is probably time to post it here... In the creation of a Blanket PO, current functionality only allows the entry of one item. In many cases, a client needs to be able to creat a blanket purchase order for a single vendor that has multiple items (SKU's) and the ability to release selected items and quantities as needed. In other situations, a client would also have a price point/contract from a vendor that if they order x number of items at a time they can obtain a better price. But again, current functionality does not allow this ...

How do I protect my comments from being edited by another user?
I regularly send excel worksheets to a co-worker via e-mail and then she sends it out to others within the company (sales force). There are occasions when she will need to revise it and send it back to me. How do I protect my comments so that they cannot be edited, or can I? I received a worksheet back today that the comments I had entered were changed. These comments are my back-up and I do not want them changed. Can anybody help? highlight the whole sheet format cells / protection / uncheck locked highlight the cells you want protected format cells / protection / check locked tool...

Using Emit to Return a Value
Hello: I am trying to write a piece of code that can implement an interface's methods simply by returning a specified value. I am trying to see how Mock libraries are written. For instance, I have a piece of code that looks like this: MockFactory.CreateMock<ISomething>().Setup(something => something.Foo()).Returns("abc"); What I want to be able to do is somehow implement ISomething.Foo so that it returns "abc". However, I am having a hard time figuring out the Emit code for taking an arbitrary object and incorporating it. I've never want...

CF to Row after CF is applied to Column
Can someone please help me? I've read through the postings and the help files but still can't seem to figure out how to do this. I've applied conditional formatting to the J column of my spreadsheet so that if there's any text listed the cell turns yellow. What I'd like is if not only the cell but the row, from A:O turned yellow also. How do you do that? I'm using 07. Any help in English walking me through the steps or a hint toward the appropriate post would be greatly appreciated. Cuz I aint figrin it out on myown! Thanks in advance! --Dax...

XML Serialisation of value/built in types
Hi, When I try to use the XMLSerializer class to serialize an object with simple types, I get node names that are the "basic" versions of the type names, ie: string testString = "hello world"; XmlSerializer ser = new XmlSerializer(testString.GetType()); StringWriter sw = new StringWriter(); XmlTextWriter xw = new XmlTextWriter(sw); ser.Serialize(xw,testString ); ....i get: <?xml version="1.0" encoding="utf-16"?><string>hello world</string> ....but what I would like is the full name of the type (ie the same as if I called string.G...

Requery one subform from another subform
Hi Groupies My Main form (frmProjectMaster) contains 2 levels of subforms. The first subform is frmCompartmentsWizard. frmCompartmentsWizard contains fsubBlocksWizard and fsubReefersWizard. On fsubBlocksWizard, there is a combo box called strStockCode that gets its data from tblReefers. tblReefers happens to be the data source for fsubReefersWizard. What I need to happen is this: When a new record is added in fsubReefersWizard, I need the combo box on fsubBlocksWizard to requery so that the new data shows up. I need to stay in fsubReefersWizard. I am having trouble figuring ou...

how to jump to the next column with "enter" in excel?
Hi, The "tab" key is not an option? "dragos" <dragos@discussions.microsoft.com> wrote in message news:076B9F2B-AE6B-4DCD-96ED-6B5AAE35DC4B@microsoft.com... > ...

Stop running sum(Over All) when customer change
Dear All I hv report based on tblTransaction which contains many customers with their transactions by date wise. I grouped report on CustomerID sub Group by Fromat(trnDate,”MMM-YY”) to get monthly transactions for each customer. I made calculated field to obtained closing balance with running sum over Group. I want to make the Running sum – over all but when customers is changing it must stop and start again, by default it will give running sum of all the customer, exactly this I don’t want. Any idea to stopped or make running sum based on customer when month is changing it ...

Subtracting time values
I am trying to calculate the difference between two cells which ar formatted with Custom [h]:mm option. It has so far worked fine unti now, the higher of the two values is over 10000:00. I now get an erro stating that the higher value is not greater than the lower value. think it is because time values are represented by a date time syste which perhaps cannot work above values of 10000:00 -- fishphon ----------------------------------------------------------------------- fishphone's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2462 View this thread: http://www...

Sum of total Time
I need to Sum the total units of time that could be either entered in thousandths or milliseconds in a worksheet. Do I need a special add in? Any tips would be gratefully accepted. thanks Hi you may provide some example data of how you enter your values currently -- Regards Frank Kabel Frankfurt, Germany "Deltaecho" <Deltaecho@discussions.microsoft.com> schrieb im Newsbeitrag news:45968003-50B1-4651-A009-BAA7664BD8AC@microsoft.com... > I need to Sum the total units of time that could be either entered in > thousandths or milliseconds in a worksheet. > Do I ne...

backup of disabled accts/mailboxes fails with veritas 9.1
does anyone know how to continue to back up someone's mailbox after their account has been disabled? (exch 2k/win2k server). T.I.A. "John Smith" <jsmith@macroshaft.com> wrote: >does anyone know how to continue to back up someone's mailbox after their >account has been disabled? (exch 2k/win2k server). > >T.I.A. > Doing a standard ntbackup will backup the entire store, regardless of whether the account the mail object refers to is disabled or not. Mark Arnold MCSA MCSE+M MVP, mark@mvps.org FAQ: http://www.swinc.com/resource/exchange.htm & http...

Why Propertysheet DoModal() always returns a wrong value?
Hi all, I have the following typical code. The Propertysheet has Ok and Cancel buttons at the bottom. When I click Ok button, it always returns IDCANCEL. But it actually executes all OnOk() functions. How I can make sure it returns the right value. Thanks a lot. CMyPropertySheet psDlg(IDS_MAIN_CAPTION, this); int nResponse = psDlg.DoModal(); if (nResponse == IDOK) { // TODO: Place code here to handle when the dialog is // dismissed with OK } else if (nResponse == IDCANCEL) { // TODO: Place code here to handle when the dialog is ...

how to merge data from multiple columns to one column #2
I have first, middle, and last names in A, B, and C. I want to merge everything into A. Ho do I do that? In column D use the formula =A1&" "&B1&" "&C1 The " " would put in your spaces in between the names. Then go to column D & do Edit Copy, move to column A & do Edit Paste. Then you should be able to delete columns B, C & D. Hope that helps! >-----Original Message----- >I have first, middle, and last names in A, B, and C. I want to merge >everything into A. Ho do I do that? >. > =CONCATENATE (text1,text2,....

Grouping Same Data in column
Hi Guys, Using: Excel 2000 Issue: Is there a way to create groupings based on same values down particular column. Example: I have phone numbers listed down column L. But some of th numbers appear more than once. Can I automatically create groups base on the same phone numbers so it would be easier on the eye to follow. Hope this makes sense. TIA Jonas :cool -- Message posted from http://www.ExcelForum.com You could sort the list by the phone numbers, and use conditional formatting to highlight the rows that contain a duplicate phone number: Select a cell in the list Choose Dat...

Separating data in a column
the spreadsheet i am working on has 1 column with 2 possible answers, i.e. yes/no. what i need to do is separate the replies and then total the yes replies in another cell and also total no replies in a different cell. How do i do it? The following COUNTIF function can be used =COUNTIF($A$1:$A$21,"yes") The sort or data filter command can be used to separate the replies. "Gemgirl" wrote: > the spreadsheet i am working on has 1 column with 2 possible answers, i.e. > yes/no. what i need to do is separate the replies and then total the yes > replie...

How do I get column headings to stay visible at all times in Exce.
I need the column headings to stay visible at all times on teh screen, and not just when you print the document? Hi! Select the ENTIRE row immediately under your column headers then goto Window>Freeze Panes. Biff "nemmettiv" <nemmettiv@discussions.microsoft.com> wrote in message news:323C229B-9E55-41C7-AB90-9021E1FBA8E4@microsoft.com... >I need the column headings to stay visible at all times on teh screen, and > not just when you print the document? Hi Select Cell A2. Windows->Freeze Panes to hold row 1 inplace or select B2. windows->Freeze Panes to ...

How to get combo box selected index changed event in another page from the existing page in winforms
Hi, I am working on windows forms project. I have two winforms (window1 and window2). I have a combobox called cmbEmail in window1 and Textbox called txtName in window2. I want to retrieve some information into the txtName in window2 when I selected something from cmbEmail in window1. If I write code to retrieve something into the txtName (which is in window2) in cmbEmail SelectedIndexchanged event in window1, I will get an error of txtName doesn't exist in this page. Can anybody help me to solve this problem.. Thanks and Regards Naresh "Naresh" &...