Summing an unknown range of cells

I'm trying to create a generalised expense claim form for a group of 
people. Each row refers to a particular expense and the user of the form 
(the claimant) adds rows as needed, so the actual number of rows is 
unknown at the time of creating the form.
What I would like to be able to do is, for example, insert the SUM 
function in a particular column to sum all the cells in the column above 
it (some may be empty) up to a particular row.
For example, suppose the SUM function is in column C then I would like 
to write something like: SUM(C9:C(*-1)) where "*" refers to the row the 
SUM function is in after the user has added an unknown number of rows 
after row 9. Then, if there were 3 expenses (rows 9, 10 and 11) the 
function would turn out to be SUM(C9:C11). I want the function to change 
itself.
Is this possible? I can't really believe nobody has needed to do this 
sort of thing before.

-- 
Regards, Gary Wooding
(To reply by email, change feet to foot in my address)
** Posted from http://www.teranews.com **
0
binswood (9)
5/28/2008 2:37:18 PM
excel 39879 articles. 2 followers. Follow

3 Replies
727 Views

Similar Articles

[PageSpeed] 56

You might find it easier to put the SUM function at the top of your
sheet, and then you can set up the formula as:

=3DSUM(C9:C65536)

That way it doesn't matter how many rows they use below C9. By fixing
the panes at row 2 or below, you can scroll down the sheet and the sum
will always be visible on the top row.

Alternatively, you could use a dynamic named range which automatically
adjusts itself to the amount of data that you have - Chip Pearson
explains how here:

http://www.cpearson.com/excel/named.htm#Dynamic

Hope this helps.

Pete

On May 28, 3:37=A0pm, lemel_man <binsw...@bigfeet.com> wrote:
> I'm trying to create a generalised expense claim form for a group of
> people. Each row refers to a particular expense and the user of the form
> (the claimant) adds rows as needed, so the actual number of rows is
> unknown at the time of creating the form.
> What I would like to be able to do is, for example, insert the SUM
> function in a particular column to sum all the cells in the column above
> it (some may be empty) up to a particular row.
> For example, suppose the SUM function is in column C then I would like
> to write something like: SUM(C9:C(*-1)) where "*" refers to the row the
> SUM function is in after the user has added an unknown number of rows
> after row 9. Then, if there were 3 expenses (rows 9, 10 and 11) the
> function would turn out to be SUM(C9:C11). I want the function to change
> itself.
> Is this possible? I can't really believe nobody has needed to do this
> sort of thing before.
>
> --
> Regards, Gary Wooding
> (To reply by email, change feet to foot in my address)
> ** Posted fromhttp://www.teranews.com**

0
pashurst (2576)
5/30/2008 12:23:00 AM
On May 29, 12:37 am, lemel_man <binsw...@bigfeet.com> wrote:
> I'm trying to create a generalised expense claim form for a group of
> people. Each row refers to a particular expense and the user of the form
> (the claimant) adds rows as needed, so the actual number of rows is
> unknown at the time of creating the form.
> What I would like to be able to do is, for example, insert the SUM
> function in a particular column to sum all the cells in the column above
> it (some may be empty) up to a particular row.
> For example, suppose the SUM function is in column C then I would like
> to write something like: SUM(C9:C(*-1)) where "*" refers to the row the
> SUM function is in after the user has added an unknown number of rows
> after row 9. Then, if there were 3 expenses (rows 9, 10 and 11) the
> function would turn out to be SUM(C9:C11). I want the function to change
> itself.
> Is this possible? I can't really believe nobody has needed to do this
> sort of thing before.
>
> --
> Regards, Gary Wooding
> (To reply by email, change feet to foot in my address)
> ** Posted fromhttp://www.teranews.com**

Maybe...
=SUM(INDIRECT("C9:C" & ROW()-1))

Ken Johnson
0
KenCJohnson (314)
5/30/2008 12:25:43 AM
lemel_man wrote:
> I'm trying to create a generalised expense claim form for a group of 
> people. Each row refers to a particular expense and the user of the form 
> (the claimant) adds rows as needed, so the actual number of rows is 
> unknown at the time of creating the form.
> What I would like to be able to do is, for example, insert the SUM 
> function in a particular column to sum all the cells in the column above 
> it (some may be empty) up to a particular row.
> For example, suppose the SUM function is in column C then I would like 
> to write something like: SUM(C9:C(*-1)) where "*" refers to the row the 
> SUM function is in after the user has added an unknown number of rows 
> after row 9. Then, if there were 3 expenses (rows 9, 10 and 11) the 
> function would turn out to be SUM(C9:C11). I want the function to change 
> itself.
> Is this possible? I can't really believe nobody has needed to do this 
> sort of thing before.
> 
Sorry for the confusion on this thread, but my "preferred" server 
(Teranews) messed it up.
I posted the original query via Teranews, but when nothing turned up 
after 3 hours, decided that Teranews was misbehaving and posted again 
via another server. I also changed the subject to "Processing an unknown 
sized array" so as to "keep tabs on it" as it were.

-- 
Regards, Gary Wooding
(To reply by email, change feet to foot in my address)
0
binswood (9)
5/30/2008 6:34:50 AM
Reply:

Similar Artilces:

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

In excel change color of a range of cells based on a cell value
Based on a cell value from a drop down box, how can you make a range of cells to change background color? Use the cell the box is linked to in your conditional formatting formula. If your box is tied to, say, A1, make sure the conditional formula is like: =$A$1="value" value is whatever will be selected in the box. Then set the background color. Squeaky "kwitt" wrote: > Based on a cell value from a drop down box, how can you make a range of cells > to change background color? ...

nth cell in the range
Within a VBA Module, I would like to set the ith component of my array A(1 to N) to be equal to the 100*(nth cell in a column in excel). How can I do this within a loop? ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** Hi try something like the following sub foo() dim c_values(1 to 100) dim i for i = 1 to 100 c_values(i)=activesheet.cells(i*100,"A").value next end sub >-----Original Message----- >Within a VBA Module, I would like to set the ith component of my array >...

Date-range specific report with counted results of combo box selections
Hi there, I'm a novice with Access, but am currently setting up a patient database for an NGO in India that I'm volunteering with. The primary reason for the database is to collect statistics quarterly. It is a simple database with one table that collects patient data from simple forms with some fields having combo boxes. Ultimately I want to generate reports that will show a summary of 'counts' of various fields for a given period. For example: In the table I have a combo box for where the person came from, with several options including - "Pick up Y. Bazar", &quo...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

Referencing a sheet by taking its name from a cell
I'm not even sure the title of this post is correct but it's as clear as I could make it! I'm creating a summary sheet of data from many other worksheets. The worksheet names are mmm-yy (e.g. Nov-07). The top row of the summary sheet will contain those same names, with the summary data in the column below. I'm trying to find a way to reference the correct sheet by utilising the name in the top row. Something like ='A2'!C7 , I suppose, but this particular attempt didn't work. Can someone help? Thanks! Paul Assuming that you have "Nov-07"(sheet name) in...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Automatic filling of cells within a certain date range
Can someone please help, I have been stuck on this for the past two days... Column 'AB' has 'Start Date' and column 'AD' has 'Finish date' Row 19, cells 'AI' to 'DW' have the months written as 'J' 'F' 'M' 'A' etc.....from 2008 to 2015. I want to be able to enter a 'start date' in column 'AB' i.e. 1 January 2008 and a 'Finish date' in column 'AD' i.e. 1 April 2015. Then i would like the cells 'J - A' on row 19 to be filled with '1' in each month.... I...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

Special format for feet
Many html tables list height as x-yy where x is feet and yy is inches. Excel imports this as a date. In looking at cell number formats there is no option to display either a feet-inches 'number' or an inches number as x-yy. (I will write up a separate item about Excel importing the feet and inches as dates.) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggesti...

Cells stay highlighted, cannot use mouse
When I click on a cell in excel, that cell shows as clicked on, but when I move the mouse all surrounding cells become highlighted as well. So I cannot input anything anywhere, as moving the mouse around just keeps highlighting cells. And I cannot shut excel down as trying to move the mouse to a menu bar keeps highlighting cells in the worksheet. The only way to get out of excel is to End Task. I have come across this problem before, but I don't know how I inadvertently activate it, and to stop it was some combined of Ctrl-Enter but I can't remember what. Hopefully someone will un...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

using intersection of ranges
Need to specify the intersection of two named ranges, but require either one or both of them to be referenced to other cells. In other words, someone would enter the range which is one or both of the two, and I need the formula to look at that cell and use its name in the intersection argument. Any ideas? I have tried TEXT, but that doesn't seem to do it. Suppose the rangenames are rangeA and rangeB, and the names are in D2 and D3 =INDIRECT(D2) INDIRECT(D3) Note the spaces between the two functions; space is the intersection operator. Now you'll have to test for one or two...

chart data from same cell on multiple sheets
how can i chart data from the same cell on multiple sheets? when i enter sheet1:sheet5!a5 i get a invalid reference error. The values for a chartseries must all come from a single chart. You need to create a summary range on a worksheet to hold the values. This page has a few hints: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ drhamann wrote: > how can i chart data from the same cell on multiple sheets? when i enter >...

Data entry & Formula in same cell
Is it possible to have a value entered into the same cell that the formula resides. For example... in cell A1 you enter 1 (for 1 foot)and this same cell calulates how many inches (A1 equals 12) One addition question is there a way to have a cell round up to the nearest even number? Thank you so much for your help. Hi Bridge 1. without VBA code it is not possible to enter a value and a formula at the same time into one cell. 2. If you number is stored in A1 use the formula =EVEN(A1) HTH Frank Bridge wrote: > Is it possible to have a value entered into the same cell > that the for...

Date ranges
Hi I have a list of predefined dates in column B in colum C formula to show todays date. (i.e. if today = 24-6-06) What I want is D1 to to show B2 (19-7-06) and D2 to show B1 (12-6-06) A B C D 1 1 12-6-06 =today() next date = 2 2 19-7-06 previous date = 3 3 24-8-06 4 4 30-9-06 Any help appreciated. Regards Paul T Paul T wrote: > I have a list of predefined dates in column B in colum C formula to show > todays date. (i.e. if today = 24-6-06) > What I want is D1 to to show B2 (19-7-06) an...

=SUM Ranges Do Not Update
I have a Excel 2000 spread sheet, with the following macro to insert new row. Sub InsertRow() ' ' Macro1 Macro ' Macro recorded 4/27/2004 ' 'GoTo label, MyString ActiveSheet.Unprotect Application.Goto Reference:="MyCell" ActiveCell.Select ActiveCell.EntireRow.Insert ActiveCell.Offset(-1, 0).Select ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAll Application.CutCopyMode = False ActiveCell.Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True End Sub The problem is that in the "Mycell&qu...

Inserting a formula into blank rows in a varying range
Hi all I have a spreadsheet where the data is updated from an outside database, and then run a macro to separate it into monthly blocks so that it ends up looking like this: Code Description Date Month Number AAA Job No 1 01/02/04 2 BBB Job No 2 05/02/04 2 CCCC Job No 3 01/03/04 3 DDD Job No 4 03/04/04 4 In other words there is a blank row on each change in Month Number. I need a macro to insert a month and year for each month into the blank line above that month's data... so that the spreadsheet would look like t...

unknown file format
If I'm sent an e-mail with an attachment of (in this case, a tombstone), that the sender doesn't have a printer and wants a picture of. All I get is a gray screen, with a little menu in the middle saying (unknown file format). Does anybody know the fix? Sincerely Jerry Johnston - jerryjohn@comcast.net Thanks On Sun, 23 May 2004 18:12:41 -0700, "Jerry Johnston" <anonymous@discussions.microsoft.com> wrote: >If I'm sent an e-mail with an attachment of (in this >case, a tombstone), that the sender doesn't have a >printer and wa...

Hyperlink to first blank cell in column?
(XL2007) Thanks to help from MVP Biff, I can return the row number of the first blank cell in a single-column named range using an array formula (http://tinyurl.com/qb689k). This is a dynamic range that will adjust as new items are added. I'm setting up a workbook in which I have a "Blank Master" sheet and a "Jobs List" sheet. The "Master" sheet will be copied and renamed for each month. I'd like to have a hyperlink on the "Master" sheet that will carry over to each new copied sheet that would take the user to the first blank cell in the JOB...

In excel how do i put zero as the first Number in a cell
how could i add zeros as the first number in a cell without a decimal Hi Two ways: Start your entry with an ', to read '083, or Format the cells where you want to insert this as text, and then enter the number "help" wrote: > how could i add zeros as the first number in a cell without a decimal > Either format the cell for TEXT (Right-click > FormatCells > NumberTab > Text > ok), or if you only have a few, just type a leading apostrophe before the leading zero.........it won't show up and will change that cell to TEXT format.......... Vaya con ...

Change Text Case to Upper in a cell range A2:A92
I have a workbook of Contacts. In column A there are Names. I wish to convert those to UPPER case. I've seen a few VB codes that change the case to UPPER if I change something in the cell, GREAT if I want to go through the entire Column and change something. But is there another way? "Barry" wrote: > I have a workbook of Contacts. In column A there are Names. I wish to > convert those to UPPER case. I've seen a few VB codes that change the case > to UPPER if I change something in the cell, GREAT if I want to go through > the entire Column and change s...

Sum value between dates problem driving me mad!
Good Evening All, I am really struggling here, have tried, nested IF's including AND's but am a bit stumped. I am fairly familiar with Arrays, but cannot seem to combine all. I have a data table (as detailed below). (Hopefully, this should be 'pastable' into Excel - it works for me) I simply wish to calculate the expected totals per month. Any help & assistance with this would be most welcome. Cheers, Mathew Note, earliest Start is 01/04/03 Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 01/04/2003 23.00 01/04/2003 09/07/2003 23.00 ...