Need a formula for multiple sums in a cloumn when rows vary

I am making a invoice I need a total for each truck, with a grand total at 
the end. The number of rows for each truck in the amount column will vary. Is 
it possible to have the text truck total to the left of the total?  A small 
example below, there will be more entries under each truck and more trucks 
also.

tr#     date            hrs rate   amount
 3       4/12/10       10  50      500
          4/13/10        5   50      250
                       truck 3 total   750

 4      4/10/10          1    50      50    
                         truck 4 total   50
 
 and so on 
          with  a  total invoice     850

0
Utf
4/21/2010 9:06:01 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1030 Views

Similar Articles

[PageSpeed] 24

Hi Cheryl

The Grand Total will simply be
=SUM(E2:E50)/2

assuming row 50 is the last row before your Grand Total
--
Regards
Roger Govier

Cheryl wrote:
> I am making a invoice I need a total for each truck, with a grand total at 
> the end. The number of rows for each truck in the amount column will vary. Is 
> it possible to have the text truck total to the left of the total?  A small 
> example below, there will be more entries under each truck and more trucks 
> also.
> 
> tr#     date            hrs rate   amount
>  3       4/12/10       10  50      500
>           4/13/10        5   50      250
>                        truck 3 total   750
> 
>  4      4/10/10          1    50      50    
>                          truck 4 total   50
>  
>  and so on 
>           with  a  total invoice     850
> 
0
Roger
4/21/2010 9:13:54 PM
Cheryl
I think the SUBTOTAL function will achieve what you require. You could try 
Excel help for an explanation but Linda Johnson gives a step by step guide: 
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=358


"Cheryl" wrote:

> I am making a invoice I need a total for each truck, with a grand total at 
> the end. The number of rows for each truck in the amount column will vary. Is 
> it possible to have the text truck total to the left of the total?  A small 
> example below, there will be more entries under each truck and more trucks 
> also.
> 
> tr#     date            hrs rate   amount
>  3       4/12/10       10  50      500
>           4/13/10        5   50      250
>                        truck 3 total   750
> 
>  4      4/10/10          1    50      50    
>                          truck 4 total   50
>  
>  and so on 
>           with  a  total invoice     850
> 
0
Utf
4/23/2010 6:12:01 AM
Reply:

Similar Artilces:

Help with a formula..
I am trying to create a formula that will take information from a cell on one sheet and combine it with text on another sheet. I know how to get the two together. My problem is that I want the part that is brought in to be bolded type. Here is what I have in the formula. ="we are pleased to submit our quotation for "&(cell reference)&" according to the following specifications." What I want to do is have the cell reference part be bold type. Is there a way to do that? It doesnt work if I bold the cell.. already tried it.. Any suggestions? Thanks! KK You'...

Help needed urgently
Hi, Im trying to check that one row of data satifies a criteria, then if it does it counts an entry in another column adjescent to it. Example: - Column A - Column B Blay - Y Blay - N Blay - Y Chop - N Chop - N High - Y What i would be asking (of a much bigger table) is If Column A = Blaydon and then column B (same row) = Y, then count. In this example I would want 2 returned as the answer. Is this possible? Thanks -- Andy Try one of these... Use cells to hold the criteria: D2 = Blaydon E2 = Y If you're using Excel 2007 or later: =COUNTIFS(A2:A7,...

Controlling multiple TCP connections from Outlook 2003 clients
I've noticed that Outlook 2003 clients can have multiple TCP connections to the mailbox store on Exchange. As the following web link claims: http://www.windowsitpro.com/Article/ArticleID/46319/46319.html "This behavior is by design. Outlook can open multiple connections to parallelize its data communications with the Exchange server. The actual number of connections will vary according to the version of Outlook that you're using and the mode in which you're using it. For Microsoft Office Outlook 2003 in Cached Exchange Mode, you'll see four connections for the mailbox, ...

Hiding empty rows and columns
Does anyone know the code for hiding all blank rows and columns in a worksheet. Thanks -- Message posted from http://www.ExcelForum.com Hi try the following (adapted from: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows) Public Sub HideBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count > 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFuncti...

Select all of a certain column across multiple worksheets
Is there an easy way to simultaneously select (or do a find-and-replace) on column H on every worksheet of an entire multi-sheet workbook, without having to select that column on each individual worksheet by hand? Charles Belov SFMTA Webmaster http://www.sfmta.com/webmaster Right-click on first sheet tab and "Select all sheets" In activesheet select the column............will be selected on all sheets. Gord Dibben MS Excel MVP On Mon, 24 Aug 2009 16:43:02 -0700, "Charles Belov" <invalid@invalid.invalid> wrote: >Is there an easy way to simultaneously se...

selected row count of list box
A2k Is there a way to get the selected row count of a list box dynamically as the user selects rows? Delphi has an event called "OnSelectionChanged" but Access is much more limited. I don't want the user to have to exit the list box or click a button or anything manual in order to see the # of rows he's selected. How can this be done? I know about "lstCusts.ItemsSelected.Count" but not sure what event to use it in to accomplish what I need. Thanks, Keith Never mind. I did this and it handles both mouse and keyboard selections: Private Sub lstCusts_AfterUpdat...

Sumproduct with multiple date criteria
Having a tough time with this one. Sheet 1 Column A = Start Date, Column B = End Date, Column C = Quantity. Sheet 2 Row A = Start Date, Row B = End Date. I would like Row C to sum quantity from sheet 1 where ever the two date ranges intersect. The date ranges on sheet 2 represent the beginning and ending of a week (Mon-Sun). Sheet 1 Column A Column B Column C 01JAN2010 24JAN2010 1,000 Sheet 2 Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010 Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010 Row c 1,000 1,000 1,000 0 Do the Sheet 2 Star...

Fill Down Excluding First Row
Greetings, I have a spreadsheet where the first row (1) consists of labels that describe the column located below each label. For example, I have calories, fat, carbs, etc. It is desirable to have some of these columns filled in automatically. For example one row might be "calories from fat," "calories from carbs," etc, and so I'm doing a "fill down" on those columns. Unfortunately, the fill downs are completing the columns with the labels. So I'm wondering if it's possible to: (a) Exclude the label row (row 1) from the fill down or, better yet;...

Trouble doing a formula for excel
Hi All I have a spreadsheet with the following A1: z:\data/pc32/tsheets\unsorder00039.csv I would like to add 1 too the number to make unsorder00040.csv and so I have try mid,right,left i can't seem to do it Cheers "Jason" <Jason@discussions.microsoft.com> wrote in message news:53AAB904-8595-499F-BF38-8BE00826101C@microsoft.com... > Hi All > > I have a spreadsheet with the following > A1: z:\data/pc32/tsheets\unsorder00039.csv > > I would like to add 1 too the number to make unsorder00040.csv and so > I have try mid,right,left i can't see...

Need code snippet to read offline PST file
Hi friends, I have a PST file in my local hard disk and have requirement to read PST file and parse through all folders and then each message item in all folders and then segregate them to different folders based on subject line. Please kindly send the code for the above requirement. Thanks & Regards Ramesh -- ramserp You're going to have to write your own code. Do you know anything about Outlook programming at all? You can start out by looking at information and code samples at www.outlookcode.com. -- Ken Slovak [MVP - Outlook] http://www.slo...

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

copying formulas in vba
Hey guys. I was wondering if someone could help me. I am writing a vba script that takes in data, analyzes it, and then copies the results to a new file. I am having a problem with two things. 1) I am using a template for the new file so there are a lot of formulas (sums and std) already defined and ready to use. However, there are some instances where there is a random amount of additional data I have to put in. So, I have to apply the same formulas to this new data. How do I copy formulas from one cell to another (allowing for a change in row) in vba? Lets say cell(1,4) has the form...

Excel formula #24
What is the formula that brings back a zero for an empty cell instead of 0 0 #DIV/0! Try =if(iserror(formula),0,formula) ************ Anne Troy www.OfficeArticles.com "Dave" <Dave@discussions.microsoft.com> wrote in message news:8392DE7F-0B65-4DEE-87F4-985133BB1976@microsoft.com... > What is the formula that brings back a zero for an empty cell instead of > 0 0 #DIV/0! > ...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

Sum amount if = 2 value's
I have a spreadsheet of payment types for which I want to sum the tota amount per type per month A B C Type Amount Month I'm able to get the total amount per type by usin =SUMIF(A:A,"TYPE",B:B), but can't work out how to get a total for eac type each month Somthing along these lines: =SUMIF((A:A,"TYPE",B:B)&C:C,"MONTH")) ???? Any idea's -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.ph...

A student figuring a formula for wages
Hello, I am beginner student trying to figure out a formula that calculates how to pay people for the number of hours they work, and at the same time figure out any overtime they may have. The wage is in cell B4, and the # of hours is in cell C4. Overtime is figured at 1.5 of the wage in B4. I must put the end result in F4. The instructor rushed through his presentation, and said to use the "if function". The assignment is due on tuesday and any help with this is greatly appreciated. -Thanks in advance ------------------------------------------------ ~~ Message posted fro...

Newbie needs help with new Exchange 2003 installation
I am TOTALLY new to Exchange. We just installed Exchange Server 2003 on a Windows 2003 domain. I have one (test) account set up and it appears to work OK but I keep getting an error message that says "Task "Microsoft Exchange Server" reported error (0x8004010F): 'The operation failed. An object could not be found.' I know, I know...How could I possibly not understand THIS message? I appear to be getting mail but I' can't be sure. How can I check to see what's causing this (and more important...what object it's looking for?) Thanks for any help! -Fran- ...

I Need an answer for this Formula
I am using excell 2007 & this formula works {=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} When i upload this workbook to a 2003 version this formula does not work I get {=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} or somthing close to this Then in the cell with this formula has a NAME error WHY & HOW could i fix The IFERROR function can only be used in Excel 2007. Try this array formula** : =LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))...

Creating formulas to calculate time on a 24 hr time clock
I have to create a spreadsheet that will calculate total hrs worked. I've tried several different ways but I guess I don't know enough about building formulas to actually make the thing work. For example, I used a basic formula D3=A3-B3+IF(A3>B3,1)*12. This works to show the time but not the number of hours worked. If an employee comes in 1/2 hour late, it does not properly calculate this. How can I correct my formula? MW I have tried your formula and it works fine (with start time in B3 and finish time in A3). Format the reult as h:mm and it's OK. Andy "MWI...

Need to show file properties dialog box
Hello I need to show the shell's file properties dialog box. I tried converting old vb6 code into vb.net but it gives me error. Here's my code: Private Structure SHELLEXECUTEINFO Dim cbSize As Long Dim fMask As Long Dim hwnd As Long Dim lpVerb As String Dim lpFile As String Dim lpParameters As String Dim lpDirectory As String Dim nShow As Long Dim hInstApp As Long Dim lpIDList As Long Dim lpClass As String Dim hkeyClass As Long Dim dwHotKey As Long D...

Email to multiple people but show only the recepient name
Is there a way to send to ten people at once but only have one name in the To: box at a time? So that the entire list of people is not sent. Use the BCC field. --� 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 searching google.groups.com and finding no answer, Terry asked: | Is there a way to send to ten people at once but only | have one name in the To: box at a time? So that the | entire list of people is not sen...

How do I get Outlook Today to show appts from multiple calendars?
I want the opening page (Outlook Today) to show the next week's appointments from 2 calendars. I can only get it to show one calendar and it defeats the purpose of having the summary. Ideas? If you provide some basic information like: version of Outlook where the two Calendars are located (eg is one a Public Folder, a shared Calendar, a sub-folder of your mail Calendar) you'll find getting a useful answer here much faster and less laborious. :-) Regards Judy Gleeson MVP Outlook in Canberra, Australia "MORE CALENDAR" <MORE CALENDAR@discussions....

using rule creates multiple entries. How can I stop this?
I'm going to give you the details of this so maybe someone can just tell me how to correct or whether it can be corrected. I've tried creating different rules, rules with exceptions, multiple rules with multiple exceptions, all I can think. Here is the scenario. Although I'm using Lisa as an example, this problem applies to every staffer in my 9 person office. Lisa is my secretary. She sends me internal email, staff questions and stuff which goes to a a "Staff" subfolder of the Inbox. I have a rule for this. She also sends me mail she has scanned. I ha...

Linking Multiple Excel Sheets
I am not an expert in Excel....!, I encountered a situation where in I had to get data from 14 excel separate sheets having same structure (differing in values). After playing aroung with excel for few minutes, I found "Import External Data" under Data. Manually I imported all the 14 sheets and in my main sheet wrote a small formula which would give a consolidated information. With this you also have a feature which refreshes data (at specified time / or when opened). There might be a different & faster way to do it, which I am not aware of!!!. Anyway this worked for me........