Sum a column of variable length?

I have to build a workbook where columns of data will be cut and pasted into
Tab 1, then the column totals need to appear automatically on Tab 2. The
problem is that the number of rows of data in Tab 1 will be variable and
unpredictable, although the columns will always start in a known row.

I might be able to do it by something like

=SUM('Tab 1'!I7:I65535)

but is there a more precise way?



0
bxxcfilm1 (33)
2/3/2005 10:10:05 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
626 Views

Similar Articles

[PageSpeed] 5

Here is one way

=SUM(OFFSET('Tab 1'!I7,,,'Tab 1'!COUNTA(I:I)-'Tab 1'!COUNTA(I1:I6)))

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Brian" <bxxcfilm@nildram.co.uk> wrote in message
news:ens8ghdCFHA.3732@TK2MSFTNGP14.phx.gbl...
> I have to build a workbook where columns of data will be cut and pasted
into
> Tab 1, then the column totals need to appear automatically on Tab 2. The
> problem is that the number of rows of data in Tab 1 will be variable and
> unpredictable, although the columns will always start in a known row.
>
> I might be able to do it by something like
>
> =SUM('Tab 1'!I7:I65535)
>
> but is there a more precise way?
>
>
>


0
bob.phillips1 (6510)
2/3/2005 10:24:29 AM
If it's the whole column you want the total for you can use:

=SUM('Tab 1'!I:I)

"Brian" wrote:

> I have to build a workbook where columns of data will be cut and pasted into
> Tab 1, then the column totals need to appear automatically on Tab 2. The
> problem is that the number of rows of data in Tab 1 will be variable and
> unpredictable, although the columns will always start in a known row.
> 
> I might be able to do it by something like
> 
> =SUM('Tab 1'!I7:I65535)
> 
> but is there a more precise way?
> 
> 
> 
> 
0
CDB (23)
2/3/2005 10:43:08 AM
Bob,

This generates an error. The "Tab 1" after the 3 commas is highlighted. I am
using XL2000.

Brian


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eKAuMqdCFHA.1836@tk2msftngp13.phx.gbl...
> Here is one way
>
> =SUM(OFFSET('Tab 1'!I7,,,'Tab 1'!COUNTA(I:I)-'Tab 1'!COUNTA(I1:I6)))
>
> -- 
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Brian" <bxxcfilm@nildram.co.uk> wrote in message
> news:ens8ghdCFHA.3732@TK2MSFTNGP14.phx.gbl...
> > I have to build a workbook where columns of data will be cut and pasted
> into
> > Tab 1, then the column totals need to appear automatically on Tab 2. The
> > problem is that the number of rows of data in Tab 1 will be variable and
> > unpredictable, although the columns will always start in a known row.
> >
> > I might be able to do it by something like
> >
> > =SUM('Tab 1'!I7:I65535)
> >
> > but is there a more precise way?
> >
> >
> >
>
>


0
bxxcfilm1 (33)
2/3/2005 12:15:54 PM
Brian,

Sorry my mistake, I added the sheets at the end,wrongly. Try this

=SUM(OFFSET('Tab 1'!I7,,,COUNTA('Tab 1'!I:I)-COUNTA('Tab 1'!I1:I6)))

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Brian" <bxxcfilm@nildram.co.uk> wrote in message
news:OZbk0neCFHA.2384@TK2MSFTNGP14.phx.gbl...
> Bob,
>
> This generates an error. The "Tab 1" after the 3 commas is highlighted. I
am
> using XL2000.
>
> Brian
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:eKAuMqdCFHA.1836@tk2msftngp13.phx.gbl...
> > Here is one way
> >
> > =SUM(OFFSET('Tab 1'!I7,,,'Tab 1'!COUNTA(I:I)-'Tab 1'!COUNTA(I1:I6)))
> >
> > -- 
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Brian" <bxxcfilm@nildram.co.uk> wrote in message
> > news:ens8ghdCFHA.3732@TK2MSFTNGP14.phx.gbl...
> > > I have to build a workbook where columns of data will be cut and
pasted
> > into
> > > Tab 1, then the column totals need to appear automatically on Tab 2.
The
> > > problem is that the number of rows of data in Tab 1 will be variable
and
> > > unpredictable, although the columns will always start in a known row.
> > >
> > > I might be able to do it by something like
> > >
> > > =SUM('Tab 1'!I7:I65535)
> > >
> > > but is there a more precise way?
> > >
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
2/3/2005 1:09:36 PM
Bob,

That works for me. Many thanks.

Brian

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:Oil$dGfCFHA.392@TK2MSFTNGP14.phx.gbl...
> Brian,
>
> Sorry my mistake, I added the sheets at the end,wrongly. Try this
>
> =SUM(OFFSET('Tab 1'!I7,,,COUNTA('Tab 1'!I:I)-COUNTA('Tab 1'!I1:I6)))
>
> -- 
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Brian" <bxxcfilm@nildram.co.uk> wrote in message
> news:OZbk0neCFHA.2384@TK2MSFTNGP14.phx.gbl...
> > Bob,
> >
> > This generates an error. The "Tab 1" after the 3 commas is highlighted.
I
> am
> > using XL2000.
> >
> > Brian
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:eKAuMqdCFHA.1836@tk2msftngp13.phx.gbl...
> > > Here is one way
> > >
> > > =SUM(OFFSET('Tab 1'!I7,,,'Tab 1'!COUNTA(I:I)-'Tab 1'!COUNTA(I1:I6)))
> > >
> > > -- 
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Brian" <bxxcfilm@nildram.co.uk> wrote in message
> > > news:ens8ghdCFHA.3732@TK2MSFTNGP14.phx.gbl...
> > > > I have to build a workbook where columns of data will be cut and
> pasted
> > > into
> > > > Tab 1, then the column totals need to appear automatically on Tab 2.
> The
> > > > problem is that the number of rows of data in Tab 1 will be variable
> and
> > > > unpredictable, although the columns will always start in a known
row.
> > > >
> > > > I might be able to do it by something like
> > > >
> > > > =SUM('Tab 1'!I7:I65535)
> > > >
> > > > but is there a more precise way?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
bxxcfilm1 (33)
2/3/2005 2:26:57 PM
Reply:

Similar Artilces:

Single/Multi-Column reports
I have a report that has job records that need to be across the whole page. With each group of records that are across the whole page, I have related employee names (from 1-4 per job) that I want to show up horizontally with their related job. My problem is with getting the employee names to show up horizontally. I've tried a 4-columned subreport but it is wanting to list all the employees from all the jobs instead of by the job. I've searched the help and can't seem to find a solution that works with a subreport or multi-column report. Any ideas would be appreciated. Thanks...

EXcel 2003
I've trying to implement the advice in John Walkenbachs book regarding formatting cells in a column in my spreadsheet if they contain the word "PLANS" Cannot get it to work Can anyone please help? hi, by exemple, select column A and doing the conditional formatting with this formula =SEARCH("PLANS",$A1)>0 -- isabelle Le 2011-07-20 11:13, kop202 a �crit : > I've trying to implement the advice in John Walkenbachs book > regarding formatting cells in a column in my spreadsheet if they > contain the word "PLANS" > Cannot get it to wor...

I'm trying to change a clustered column to a stacked column
I click on clustered and the columns don't change to reflect the gradient What do you mean by "to reflect the gradient"? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "petty" <petty@discussions.microsoft.com> wrote in message news:58AC32B4-0445-4231-8914-6C17784782D8@microsoft.com... >I click on clustered and the columns don't change to reflect the gradient ...

Programmable Column Sequencing?
I am currently using Excel 2003 and 2007 if necessary. I would like the ability to configure the excel sheet column sequencing when I hit the enter key or tab key. Is this possible?? Any suggestions??? Thank You, G Can you illustrate what you mean by column sequencing? Do you mean rearrange the column header designation or do you mean sort the data in the columns or do you mean move the data in the columns from one column to another? Examples help. "G" <G@discussions.microsoft.com> wrote in message news:4F721629-B91C-4DDC-B54C-D19A6239B7B9@m...

Excel 2007: Huge Spreadsheets (>1 Million Rows, 16,000 Columns)
Well, I don't yet have my copy of MS-Excel 2007, but I am happy to see that (in theory at least) the spreadsheets one will be able to generate will be much larger than the 256-column, 65,536 (?) row spreadsheets of the MS-Excel I now have. According to Microsoft: [Excel 2007 provides] "the ability to create single sheets of over a million rows and 16,000 columns." http://www.microsoft.com/uk/atwork/office/excel2007.mspx Well, I certainly hope this is true! It would be very useful to me. I wonder if anyone has tried to work with this feature? -- Brett http://www.FreewareFri...

Sum if cell = grey
Hi, Is it possible to have sum if cell = a colour? If for example from row A1 to 20 I have a list of numbers each with or without a cell fill colour as required. Is there a formula, (not code), I can use to calculate the sum of all the cells coloured in grey for example (colour index 15)? Thanks LiAD How did the cells get to be colored? Manually..........you will need code. Conditional Formatting? SUMIF or SUMPRODUCT by same criteria. Gord Dibben MS Excel MVP On Fri, 19 Feb 2010 07:31:03 -0800, LiAD <LiAD@discussions.microsoft.com> wrote: >Hi, ...

Convert Column Letters to Number
Hi! I'm looking for a function to convert a "large" column letters (up t 10 characters) to it's equivalent in number. e.g. Input=A Output=1 Input=AAA Output=703 Input=EXCEL Output=2,708,874 Any suggestions? Regards -- Message posted from http://www.ExcelForum.com Here's a VBA function that gives the results you want: Option Explicit Function ColumnToNumber(sText As String) As Variant Dim Bytes() As Byte Dim Letter As Long Dim Multiplier As Double Dim N As Double Dim Total As Double ColumnToNumber = CVErr(xlErrValue) If L...

Sum Non Grouped Data
I have a spread which stores past and present data. I want to be able to show a sum of the data that is visible before and after grouping is applied. I have some code which runs when an autofilter is used (see below), but this doesnt work for grouping. Public Function SumVisible(rng) Dim CellSum As Long Dim Cell As Range Application.Volatile CellSum = 0 Set rng = Intersect(rng.Parent.UsedRange, rng) For Each Cell In rng If IsNumeric(Cell) Then If Not Cell.EntireRow.Hidden And Not Cell.EntireColumn.Hidden Then CellSum = CellSum + Cell End If End If Next Cell SumVisible =...

Variable counting of rows
I’ve got these few lines of code that work fine on for a group of cells I’m calling “Large” that starts on row 5: Columns("A:A").Select Selection.Find(What:="Large", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(2, 1).Range("A1").Select NumRows = Range("B5", Range("B5").End(xlDown)).Rows.Count For x = 1 To NumRows If ActiveCell.Offset(0, -1) = "SEL" Or Active...

Customize global adress list column in a new mail
Hi! (I have Outlook 2007 and Exchange 2007). when i create a new mail message, hit the "To" button, my global adress list window appear when column by default: Name, Title, business Phone, Location, E-mail address, Company and Alias. Those column from Active directory of course. My question is: can I add/remove column in this window? For example adding "department" from Active directory? thx Since this is customized by your Exchange Admin, I would start by asking him/her if it is possible. This question can most likely be answered in an Exchange or W...

Column
I have difficulty with Word... I have a letter with column. This is my document type. But I would like to cut and paste this one page in other new document. So when I paste my page (with column in top) my document became in 2 pages. I can't put my document in just one page...help me please ! ...

Macros for find and replace and then joining columns
I have a large dataset where I need to do a find and replace for every group of 3 columns. Each column as either a 1 or 0. I need to change all the "1s" in N columns to "1", the "1s" in N+1 columns to "2", and the "1s" in N+2 columns to "3". Then I need to merge each group of 3 columns into one column, preserving the data. Any ideas? ...

csv with parent-child columns to xml
Can any of the commercial conversion tools convert csv files containing "relational" data to xml in an automated fashion? I looked at the websites for stylussudio and altova, but it looks like I would have to do some xslt coding for each file. I don't mind filling in some configuration forms for each project, I just don't want to have to write code for each one. I have hundreds of these to do over the next months. I wrote a c# program using an xmldocument, but it requires a lot of hard coding for each project. Each csv file has different columns but they all contain some pa...

conditional formatting rows depending on a column
how to conditional format a n*m box depending a column value for example I have 4*3 box and I want to highlight the row depenidng the third column value. a,f,3 a,b,4 x,y,5 x,a,1 the formula for conditional formatting is "=c1>3" and I choose color. and apply to $A$1:$C$4 You might expect only C column is colored. How can I color the A and B column? I don't want to use VBA. Select all columns and use =$C1>3 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "news.microsoft.com" <whoknows@microsft.com> wrote in me...

how can I have a formula result based on multiple criteria/columns
I have an expenses sheet set up as follows: A/status B/date C/expense type D/amount 1 allowed 05/07/06 car £20.00 2 notallowed 05/07/06 car £450.00 3 notallowed 05/07/06 car £15.00 4 notallowed 05/07/06 car £26.00 5 allowed 05/07/06 post £20.00 6 allowed 05/07/06 post ...

Multiple Column Sorting
I've been reading online about sorting multiple columns and I'm confused. Selecting one column to sort also selects all the other columns as well, at least in Excel 2000 and XP. I didn't see any difference in selecting one column or using the "Then by" boxes, in fact, the "Then by" choices didn't work. If the first column sorted in ascending order, and I chose to sort the second and third columns to sort in descending order, Excel still sorted all three columns in ascending order. If Excel sorts the all the columns together by default, why use the "Then...

Graph with variable data length
Hello all. I have a a graph inside an Excel sheet. The graph displays some columns that holds numbers. There is another program (Matlab) that writes the numbers into these columns. I want the graph to show _all_of_the_range_of_the_columns_ The problem is that I can not predict the length of the data. How can I configure the Excel graph to show the whole range of data? (The Matlab can write somewhere (in some cell) the length of the data. Can this help in some way? -- snoach ------------------------------------------------------------------------ snoach's Profile: http://www.excel...

How can you define Page name as a function of other variables.
I'm using visio 2002. I am able to include the value of the "Page name" to define anothe variable, simply by referencing "pagename()" in the formula. For example:thetext=pagename()&"hello" However, is it possible to define the "Page name" as a function o other values on the sheet? For example: Could I define the page name in this fashion : Pagename()= "hello"&shapetext(bluebox.39!thetext) where "bluebox.39" is a shape on the sheet. If so where do I do this..? I can't find the pagename cell on the pag shapeshee...

Turn 3 columns into 1
--____TRBQFUNOYCJHJLNRIYFH____ Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: base64 Content-Disposition: inline; modification-date="Wed, 13 May 2008 10:57:51 -0500" SSBoYXZlIDMgY29sdW1ucyB0aGF0IEkgd291bGQgbGlrZSB0byBiZSBtZXJnZWQgaW4gdG8gb25l LiBGb3IgZXhhbXBsZToNCg0KYjI9MyBjMj00IGQyPTYsIGFuZCBzbyBlMiA9IDMNCiAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICBlMyA9IDQNCiAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICBlNCA9IDYNCg0KVGhhbmtzIGZvciBhbnlvbmUgdGhhdCBjYW4gaGVs cA== --____TRBQFUNOYCJHJLNRIYFH____ Content-Type: multipart/related; boundary="____POUSE...

Need formula for multiple conditions in 3 columns
have a spreadsheet with several columns -- some data, some dates. Does anyone know how I can create a formula to do the equivalent of filtering on three different columns with a different date for each column filtered? For example I have four columns of information but only want to extract a certain criteria where as Date A is greater than a specific date, Date B = another date and Date C = yet another date. Date A Date B Date C Data 2/12/2001 2/23/2006 3/31/2006 Apples 2/23/2006 1/1/1900 Pears...

Sorting several columns
Hi, I'm trying to sort a sheet, sorting first by column A, then B, then C It works fine half of the sheet, but then column C isn't sorted correctly. Data format is the same, so that's not the problem. In column C sorting I've made my own list to sort by (st, 1, 2, 3, 4), and it works fine the first 20 rows. Then it only sorts correctly in column A and B example A B C AA 1A st AA 1A 1 AA 2A AB 1A 1 AB 1A 5 AB 1A AB 1A st AB isn't sorted correctly according to the list I've made in the ...

How do I convert from text to columns automatically on import?
I have a CSV file, comma delimited, where each piece of data is surrounded by double quotes. Here's an example: "Yann Contratto","","Révision","11/01/2005","20000 - PROJETS INTERNES- Div. Exp.&Cons.","20000","2.00","0242 - Soumission SERVICES","$0.00","","$0.00"," N/A" In Excel 2003 it imports into unique columns and everything works fine. But for a client of mine using Excel 2002 - it imports as you see it above. So then he has to convert text to columns ...

how to set up a column in excel so it can auto number
I am trying to auto number a column in excel so I do not have to do it. I tried to Format the cell but i don't see an option to do so. Just type 1 in A1 and 2 in A2 (or any two numbers of whatever sequence you want)..........then highlight BOTH of them and grab the little black square in the bottom right corner of A2 and drag it down the column.........if you want to autofill from there on, one row at a time as you use them, you will need a macro....... Vaya con Dios, Chuck, CABGx3 "Excel questions" <Excel questions@discussions.microsoft.com> wrote in message news:8A...

Reporting Services SUM(IIF(...) Problem
I have on my report a list grouped by username. What I need is to count the number of times the grouped username appears in another column within the dataset (both inside and outside the grouping). I have tried everything I can think of (which is why I am here.) What I have so far is some code and a "SUM(IIF(..." statement. In the code below you will find the values I use to get/set the list's current username and the values of the related text boxes. The code to set and retrieve the username work. (I can set the value of a textbox inside the list to =Code.Username and get...

Change all values in an excel column at once?
I have an Access database I'm attempting to modify a bit in Excel. It has over 16500 rows. I want to add 30 to all 16500+ values in a certain column. Is there any way to do this in one step, or am I doomed to manually change them one at a time? Enter 30 in a cell. Edit>Copy Select your cells to be updated, Edit>Paste Special, check Add. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Sn0wman" <Sn0wman@discussions.microsoft.com> wrote in message news:C902BB6C-0AB0-495A-ABBF-B222DE017259@microsoft.com... >I have an Access database I'm attempting ...