Reply:

Sum Based on Exception
The results of the following formula is 0 when column G has values in it. =SUM(IF($B$6:$B$65536="John Doe",IF($B$6:$B$65536="Jane Dane",IF($B$6:$B$65536="Jack Dark",IF($G$6:$G$65536<>"",0,1))))) Can someone please show me what I am missing or don't need in the formula in order to get the right sum? -- Cue Can you elaberate more on what you are trying to accomplish? It look more like your trying to count how many names other then the ones listed there are. Help me understand what your trying to do. -- If this reply was helpful, please...

summing time
Hi i am importing a worksheet from excel the workshhet contains data of work operations, faults that occurr the start time, end time and duration of the stoppage What i am trying to do sum the downtime against each operation for each fault the format of the data in the tables is start time /06/2007 20:32:12 end time 05/06/2007 20:39:25 duration i have wrote a query that sums the duration problem is the time format the answer returns 03/01/1900 11:32:12 i would like it to show hh:nn:ss 83:47:59 can anyone point me in the right direction please thanks kevin It appears you are usi...

Summing data referencing incremental cells
Novice Excel 03 user. I have daily production totals in row 6 starting in column C and going across. In row 83 I would like to start a weekly sum of the data from row 6. What can I do so that when I sum C6:C10 in C83 I can copy it to D83 and sum C11:C15? I believe that I need to reference the number of cells to be summed, but what about short weeks? Is there anything that can recognize sequential dates above the daily totals so that it knows to stop the sum if the work week is a 3 or 4 day week? Any help would be greatly appreciated. -- Mike Where can we read the date appropriat...

sum by coding
hi i like to add the two cells by using macro and to display the result in other cell One way: Public Sub Add2Cells() Range("C1").Value = Range("A1").Value + Range("B1").Value End Sub In article <1dcc556d-bef8-4c31-a9bb-192ba4e8e345@71g2000hse.googlegroups.com>, kaja48380@gmail.com wrote: > hi > i like to add the two cells by using macro and to display the result > in other cell ...

Sum formula #3
Im trying to sum a column, but I only want to sum values were there is a certain value in another column, ie I want to sum each value in one column where it does not = R in the other column. Hi =SUMIF(RangeToCompare,"<>R",RangeToSum) -- (When sending e-mail, use address arvil@tarkon.ee) Arvi Laanemets "MRG" <anonymous@discussions.microsoft.com> wrote in message news:118b01c3fb7f$d9775280$a001280a@phx.gbl... > Im trying to sum a column, but I only want to sum values > were there is a certain value in another column, ie I > want to sum each valu...

Sum across Worksheets
Does anyone know of a way to easily sum a single cell across many worksheets? On an individual sheet you would do something like this =Sum(A1:E1) but how bout across worksheets =Sum(Sheet1!A1:Sheet3!A1) this does not work. thanks for you help rob Hi Mortini try =SUM(Sheet1:Sheet3!A1:E1) HTH Frank mortini wrote: > Does anyone know of a way to easily sum a single cell across many > worksheets? > On an individual sheet you would do something like this > =Sum(A1:E1) > > but how bout across worksheets > =Sum(Sheet1!A1:Sheet3!A1) > this does not work. > > thank...

Summing a range that changes
Hi, I have a formula that sums the range Cell B1 = Sum(A1:$A$100) and this is copied down to cell B100, so Cell B33 = Sum(A33:$A$100) But I want the sum range to change, based on the value in cell C1. If C1 = 100, then the sum range will = Sum(A1:$A$100), Cell B33 = Sum(A33:$A$100) If Cell C1 = 300, then the sum range will = Sum(A1:$A$300) Cell B33 = Sum(A33:$A$300) I tried using indirect but I could not figure it out. Thanks for your help Take the $ out of the equations. $ = absolutes, which tells excell that those particular values do not change. "Jeff" wrote: > Hi, ...

Summing Data based upon a condition
Hi, Trying to "Sum" data in one column (A) based upon a condition in another column (B)....... Any help? -- Chris Take a look SUMIF function in help menu "Chris" wrote: > Hi, > > Trying to "Sum" data in one column (A) based upon a condition in another > column (B)....... Any help? > > -- > Chris Hi, Let's assume you want to sum column B if column A equals customer "A" =sumproduct(--($A$1:$A$1000="A"),$B$1:$B$1000) "Chris" wrote: > Hi, > > Trying to &q...

Summing multiple tables
I am trying to use a query to sum up multiple tables. When run, it should have like 14 fields with each having a sum total for a table. I have tried doing this up to 3 tables (took a long while), but it didn't work for 4 tables; I just get a blank observation in the query view. So my questions are if there is a way to do this for 14 tables and if this will take up a lot of memory? Thanks, Henry Since each of these tables is independent of the others, you should be using 14 queries. What is the point of doing the summation in a single query? If you are doing this for a report or ...

Summing Time #2
I wish to calculate the time the machine has actually run. D1 = 22:00 (Start Time) F1= Time Run E1 = 00:15 (Finish Time) D2 = 00:15 (Start) F2= Time Run E2 = 02:30 (Finish) D9 = 22:20 (Breakdown) E9 = 22:30 (Restart) D10 = 23:15 (Breakdown) E10 = 23:30 (Restart) D11 = 00:30 (Breakdown) E11 = 02:00 (Restart) Is there a formula that can scan a range (D9:E11) and give me tha answer in F1 of the time run between the Start & Finish times any help would be appreciated Pete Try: =SUM(E1:E2-D1:D2,-(E9:E11-D9:D11),--(E1:E2<D1:D2),-- (E9:E11<D9:D11)) Array-entered (press ctr...

sum value then insert row
How can I make for loop? the conditions are: if total value column height = 5 and column Article = "bike" then copy second row to row 6 (therefore 1 row added between file 5 and 6) thanks for your response ...

SUM Function over some #N/A cells
If I am using the following function: =SUM(B1:B50) most of those cells contain numbers but a few might be contain a valu of #N/A or #Value! therefore the results is #N/A or #VALUE! for thi function. I want the total of the actual numbers and ignore th errors. What can I do -- Message posted from http://www.ExcelForum.com Use the following array formula =SUM(IF(ISERROR(B1:B50),0,B1:B50)) Since this is an array formula, you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the...

Pivot Table sum option
I select the custom; sum option when selecting a field name iin my pivot table. The field is summed at the end of the pivot table but only a partial list is shown. The field names are alphabetical but only A-G then it repeats the same list A-G a second time. Can you explain what how I can show sums for all pivot table fields and why it is repeating? Thank you, Richard McDonald ...

Time Difference Calculation and Sum Problem
I have a spreadsheet wherein we log the Start Time of an event, and the End Time of the same event. I currently have 539 rows, starting in row 2 of data formatted as hh:mm. What is needed is to calculate the elapsed time, which I can do, and to calculate the total elapsed time for all 539 rows, which eludes me. Perhaps the method for calculate elapsed time for individual rows might need to be changed, or formats are incorrect to accomplish this, but I just can make no sense of the sum of all the elapsed times. A small sample of data would be: Start End 14:35 16:22 07:35 11:45 22:50 0...

Summing Range
HI, i am looking for a way to sum columns of data, until they hit a specific number, then after that point quit summing them and remove the data not needed. A B C D E F G H ID Ja Fe Ma Ap Ma Ju Tot 23 4 8 6 9 1 28 so basically i want it to sum column B and C, check to see if its totally 24, if not continuing summing until you hit 24. Then delete the values over 24. thoughts? thanks for any ideas! -- Angel ...

Summing columns
I am setting up an array table to find a particular value. The following is the formula I am using. =VLOOKUP("4 OT Double Rate",WorkComp!$A$1:$AW$34 (HLOOKUP(A7,WorkComp!$A$1:$AW$2,2,0)),0) If the is no match in column "A" it returns a value of #N/A. Now when i try to sum my columns the sum returns a value of #N/A. Is there a way to sum this value or to get the return value to return 0 so it can be summed -- Message posted from http://www.ExcelForum.com have a look in HELP index for ISNA -- Don Guillett SalesAid Software donaldb@281.com "Debbie Dies >" ...

Conditionally summing data from two columns depending on their value.
I have two columns of data: mileage in A2:A20 and number of passengers in B2:B20. Each row corresponds to a particular trip. I wish to sum the number of passenger-miles for trips that have traveled within a distance range of my discretion (the lower and upper bounds are specified in A22:A23). How would I do this? Thanks! One way... =SUMIF(A2:A20,">="&A22,B2:B20)-SUMIF(A2:A20,">"&A23,B2:B20) -- Biff Microsoft Excel MVP "Andy" <akk333@gmail.com> wrote in message news:76fe792a-9836-4f74-8056-14b2b7f632a5@h14g2000pri.goo...

Summing number in one cell and code in another cell
Hi all, the problem is, A B C D E F 10 Z7DN 3 ZAPO 4 ZBALL 5 Z7DN I would like to add all occurrences of Z7DN in E so it adds up A example total Z7DN is 15 for the column A total ZAPO =3 etc thanks.... =SUMIF(E:E,E1,A:A) etc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "pano" <jenawyn@netspace.n...

Summing Data In A Report
I am trying to write a report where records are selected for pieces of equipment. In a linked table I record the date and amount of oil added to each piece of equipment. If the oil is completely changed out I record the date of the oil change, the amount of oil added and tick a check box to indicate that the oil was changed. I have a report that shows the details for each piece of equipment. I need to add a sub report to sum the oil added from the last time the oil was changed, not including the changed amount. How do I Sum the oil added after the last oil charge, not including the quan...

sum function showing #value
hi.. have a excel sheet that someone on the group was kind enough to help with. now got another issue wonder if someone can help in one column i have a validation drop box selection in another i have an output based on a vlookup that gives a value based on the option chosen from the validation dropdown box for the vloockup i use this syntax "=IF(ISNA(VLOOKUP(A8,$M$9:$N$27,2,FALSE)),"",IF(VLOOKUP(A8,$M$9:$N$27,2,FALSE)="0","0",VLOOKUP(A8,$M$9:$N$27,2,FALSE)))" so that no value is shown in box prior selection from drop box. the sum function sums th...

Best Approach (psuedocode) for summing structure element values
I'm dealing with an Excel worksheet which allow the user to enter a values into a year/date column and an amount column. If the user enters multiple years then I need to sum the values. I thought this might be easier in VB so what I had in mind was: 1) Create two identical structures (type) of: Type TWSInfo thisYr As Integer thisValue As Single End type Dim TThisWS(rowcount) as TWSInfo Dim TThisSum(rowcount) As TWSInfo 2) Read in each Excel row into the structure 3) Sort the structure by Year 4) Loop the struct...

How to sum few items
Hi A B X 1 Y 1 X 2 Y 2 Y 4 X 5 I want to sum up those numbers in B column which contains Y in their opposite in A column. -- Little Master ------------------------------------------------------------------------ Little Master's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28227 View this thread: http://www.excelforum.com/showthread.php?threadid=479050 Use the SUMIF function eg =SUMIF(A1:A6,"=Y",B1:B6) =SUMIF(A1:A10,"Y",B1:B10) -- HTH RP (remove nothere from the email address if mailing direct) &q...

Sum with 2 if's
I have a worksheet with the following data which are all results of formulas in each cell. On another worksheet, I need to sum all the amounts in Col C if Col A=1 AND Col B=No. Can anyone helpwith this in Excel 2003? Thanks. Here's some of my data: No No $- 1 Yes $7,011.08 1 Yes $1,017.77 No No $- 1 Yes $23,205.00 1 Yes $68,300.82 1 Yes $24,477.37 1 Yes $7,023.52 1 Yes $739.25 1 Yes $16,977.94 No No $- 1 Yes $14,056.64 1 Yes $6,949.76 1 No $8,890.43 No No $17,287.55 1 No $776.36 1 Yes $18,512.61 1 No $21,168.08 1 Yes $...

Running Sum of cell in many spreadsheets
I have a workbook that is comprised of daily bank deposits. Each worksheet has a cell H3 that has the total money collected. I would like to create a worksheet that will add the value in every H3 cell in the workbook. The formula must capture all worksheets in the workbook as they are added. In other words, I'd like a total of deposits for the year on an ongoing basis. Can you also create a graph of the totals by month? The tab on each worksheet reflects the date of the deposit (Mar 8 2010) or (Mar 5-6 2010). Insert two new sheets, and name one of them "start&qu...

how do I sum only visible data in a column
I have some rows manually hidden, Please is there a formula I can apply to return the sum of the unhidden data contain in a column? Hi Afolabi, If you don't want to use VBA (create a UDF), you could review the following pages: http://www.jkp-ads.com/Articles/ExcelNames08.htm http://www.jkp-ads.com/Articles/ExcelNames09.htm In any case, there is a fundamental issue with both approaches: since hiding/unhiding rows doesn't trigger any event, such event will have to be forced from time to time, or maybe you can leave with the formulae updating their result in the next recalculation...