Totaling columns

I was so impressed with the wonderful responses and spot on answers to my 
first question on this site I thought I would give this one a try.  I did try 
to research to find this on the site, but perhaps I am wording my search 
incorrectly.
My question is:  On my download I run a macro that I have set up do 
calculations on my worksheet.
1.Each time the report is ran it would have varying  information and  
varying numer of lines.
For each run of the report  the columns below would a have the following in 
common, but again each run would have a varying amount of rows)
2.  Col A will have some blanks in the cells
3.  Col B, C, D & E will have the same number of rows (no blanks in cells)
4.  Col G, H & I will have the same number of rows (no blanks in cells)
5.  Col K, L, M, & N will have the same number of rows (no blanks in cells)
6.  Col P,Q,R,S & T will have the same number of rows (no blanks in cells)

· I would like to go to the last cell with data of each column individually 
(E, I, N, &  Q) and put the totals on the line below. (totals will not be on 
the same row).  
·   Also as an alternative, I may like the totals all on the same row (find 
the last cell with data, go to the next row below and total those columns all 
on the same row.
·  Whew, I think that’s about all.  
Thanks for trying to solve this.
John
0
Utf
6/5/2010 2:22:54 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
849 Views

Similar Articles

[PageSpeed] 46

hi
something like this might work for you..
Dim lr As Long
lr = [E2].End(xlDown).Row
[E2].End(xlDown).Offset(1, 0).Formula = "=SUM(E2:E" & lr & ")"

you would only need to dim lr once but would need to re assign the row value 
for each formula since you say they are all different. the above is for E 
column

lr = [I2].End(xlDown).Row
[I2].End(xlDown).Offset(1, 0).Formula = "=SUM(I2:I" & lr & ")"

the above would be for I column. and you would need the two lines above for 
each column that you wanted to put the formula in. just adjust the cell 
references.
the above assumes no blanks in column. if you ever do have  blanks in the 
data, you can find the last row from the bottom ie...

lr = [E65000].End(xlup).Row
or
lr = cells(rows.count, "E").end(xlup).row

regards
FSt1

"John" wrote:

> I was so impressed with the wonderful responses and spot on answers to my 
> first question on this site I thought I would give this one a try.  I did try 
> to research to find this on the site, but perhaps I am wording my search 
> incorrectly.
> My question is:  On my download I run a macro that I have set up do 
> calculations on my worksheet.
> 1.Each time the report is ran it would have varying  information and  
> varying numer of lines.
> For each run of the report  the columns below would a have the following in 
> common, but again each run would have a varying amount of rows)
> 2.  Col A will have some blanks in the cells
> 3.  Col B, C, D & E will have the same number of rows (no blanks in cells)
> 4.  Col G, H & I will have the same number of rows (no blanks in cells)
> 5.  Col K, L, M, & N will have the same number of rows (no blanks in cells)
> 6.  Col P,Q,R,S & T will have the same number of rows (no blanks in cells)
> 
> · I would like to go to the last cell with data of each column individually 
> (E, I, N, &  Q) and put the totals on the line below. (totals will not be on 
> the same row).  
> ·   Also as an alternative, I may like the totals all on the same row (find 
> the last cell with data, go to the next row below and total those columns all 
> on the same row.
> ·  Whew, I think that’s about all.  
> Thanks for trying to solve this.
> John
0
Utf
6/5/2010 4:34:30 AM
Thank you that works perfectly. I appoligize, I did forget to include that it 
is  possible that there would be no data to total in column  I, N, &  Q. Is 
there a way to accomodate for that scenerio so it does not error out?
John


"FSt1" wrote:

> hi
> something like this might work for you..
> Dim lr As Long
> lr = [E2].End(xlDown).Row
> [E2].End(xlDown).Offset(1, 0).Formula = "=SUM(E2:E" & lr & ")"
> 
> you would only need to dim lr once but would need to re assign the row value 
> for each formula since you say they are all different. the above is for E 
> column
> 
> lr = [I2].End(xlDown).Row
> [I2].End(xlDown).Offset(1, 0).Formula = "=SUM(I2:I" & lr & ")"
> 
> the above would be for I column. and you would need the two lines above for 
> each column that you wanted to put the formula in. just adjust the cell 
> references.
> the above assumes no blanks in column. if you ever do have  blanks in the 
> data, you can find the last row from the bottom ie...
> 
> lr = [E65000].End(xlup).Row
> or
> lr = cells(rows.count, "E").end(xlup).row
> 
> regards
> FSt1
> 
> "John" wrote:
> 
> > I was so impressed with the wonderful responses and spot on answers to my 
> > first question on this site I thought I would give this one a try.  I did try 
> > to research to find this on the site, but perhaps I am wording my search 
> > incorrectly.
> > My question is:  On my download I run a macro that I have set up do 
> > calculations on my worksheet.
> > 1.Each time the report is ran it would have varying  information and  
> > varying numer of lines.
> > For each run of the report  the columns below would a have the following in 
> > common, but again each run would have a varying amount of rows)
> > 2.  Col A will have some blanks in the cells
> > 3.  Col B, C, D & E will have the same number of rows (no blanks in cells)
> > 4.  Col G, H & I will have the same number of rows (no blanks in cells)
> > 5.  Col K, L, M, & N will have the same number of rows (no blanks in cells)
> > 6.  Col P,Q,R,S & T will have the same number of rows (no blanks in cells)
> > 
> > · I would like to go to the last cell with data of each column individually 
> > (E, I, N, &  Q) and put the totals on the line below. (totals will not be on 
> > the same row).  
> > ·   Also as an alternative, I may like the totals all on the same row (find 
> > the last cell with data, go to the next row below and total those columns all 
> > on the same row.
> > ·  Whew, I think that’s about all.  
> > Thanks for trying to solve this.
> > John
0
Utf
6/5/2010 10:12:23 AM
hi
i covered that scenerio with comments at the bottom of my post. simply 
substitute the line of code i suppied.

regards
FSt1

"John" wrote:

> Thank you that works perfectly. I appoligize, I did forget to include that it 
> is  possible that there would be no data to total in column  I, N, &  Q. Is 
> there a way to accomodate for that scenerio so it does not error out?
> John
> 
> 
> "FSt1" wrote:
> 
> > hi
> > something like this might work for you..
> > Dim lr As Long
> > lr = [E2].End(xlDown).Row
> > [E2].End(xlDown).Offset(1, 0).Formula = "=SUM(E2:E" & lr & ")"
> > 
> > you would only need to dim lr once but would need to re assign the row value 
> > for each formula since you say they are all different. the above is for E 
> > column
> > 
> > lr = [I2].End(xlDown).Row
> > [I2].End(xlDown).Offset(1, 0).Formula = "=SUM(I2:I" & lr & ")"
> > 
> > the above would be for I column. and you would need the two lines above for 
> > each column that you wanted to put the formula in. just adjust the cell 
> > references.
> > the above assumes no blanks in column. if you ever do have  blanks in the 
> > data, you can find the last row from the bottom ie...
> > 
> > lr = [E65000].End(xlup).Row
> > or
> > lr = cells(rows.count, "E").end(xlup).row
> > 
> > regards
> > FSt1
> > 
> > "John" wrote:
> > 
> > > I was so impressed with the wonderful responses and spot on answers to my 
> > > first question on this site I thought I would give this one a try.  I did try 
> > > to research to find this on the site, but perhaps I am wording my search 
> > > incorrectly.
> > > My question is:  On my download I run a macro that I have set up do 
> > > calculations on my worksheet.
> > > 1.Each time the report is ran it would have varying  information and  
> > > varying numer of lines.
> > > For each run of the report  the columns below would a have the following in 
> > > common, but again each run would have a varying amount of rows)
> > > 2.  Col A will have some blanks in the cells
> > > 3.  Col B, C, D & E will have the same number of rows (no blanks in cells)
> > > 4.  Col G, H & I will have the same number of rows (no blanks in cells)
> > > 5.  Col K, L, M, & N will have the same number of rows (no blanks in cells)
> > > 6.  Col P,Q,R,S & T will have the same number of rows (no blanks in cells)
> > > 
> > > · I would like to go to the last cell with data of each column individually 
> > > (E, I, N, &  Q) and put the totals on the line below. (totals will not be on 
> > > the same row).  
> > > ·   Also as an alternative, I may like the totals all on the same row (find 
> > > the last cell with data, go to the next row below and total those columns all 
> > > on the same row.
> > > ·  Whew, I think that’s about all.  
> > > Thanks for trying to solve this.
> > > John
0
Utf
6/5/2010 12:54:17 PM
Sorry, Yes that works and will save me alot of time,  Thank you very much
-- 
John


"FSt1" wrote:

> hi
> i covered that scenerio with comments at the bottom of my post. simply 
> substitute the line of code i suppied.
> 
> regards
> FSt1
> 
> "John" wrote:
> 
> > Thank you that works perfectly. I appoligize, I did forget to include that it 
> > is  possible that there would be no data to total in column  I, N, &  Q. Is 
> > there a way to accomodate for that scenerio so it does not error out?
> > John
> > 
> > 
> > "FSt1" wrote:
> > 
> > > hi
> > > something like this might work for you..
> > > Dim lr As Long
> > > lr = [E2].End(xlDown).Row
> > > [E2].End(xlDown).Offset(1, 0).Formula = "=SUM(E2:E" & lr & ")"
> > > 
> > > you would only need to dim lr once but would need to re assign the row value 
> > > for each formula since you say they are all different. the above is for E 
> > > column
> > > 
> > > lr = [I2].End(xlDown).Row
> > > [I2].End(xlDown).Offset(1, 0).Formula = "=SUM(I2:I" & lr & ")"
> > > 
> > > the above would be for I column. and you would need the two lines above for 
> > > each column that you wanted to put the formula in. just adjust the cell 
> > > references.
> > > the above assumes no blanks in column. if you ever do have  blanks in the 
> > > data, you can find the last row from the bottom ie...
> > > 
> > > lr = [E65000].End(xlup).Row
> > > or
> > > lr = cells(rows.count, "E").end(xlup).row
> > > 
> > > regards
> > > FSt1
> > > 
> > > "John" wrote:
> > > 
> > > > I was so impressed with the wonderful responses and spot on answers to my 
> > > > first question on this site I thought I would give this one a try.  I did try 
> > > > to research to find this on the site, but perhaps I am wording my search 
> > > > incorrectly.
> > > > My question is:  On my download I run a macro that I have set up do 
> > > > calculations on my worksheet.
> > > > 1.Each time the report is ran it would have varying  information and  
> > > > varying numer of lines.
> > > > For each run of the report  the columns below would a have the following in 
> > > > common, but again each run would have a varying amount of rows)
> > > > 2.  Col A will have some blanks in the cells
> > > > 3.  Col B, C, D & E will have the same number of rows (no blanks in cells)
> > > > 4.  Col G, H & I will have the same number of rows (no blanks in cells)
> > > > 5.  Col K, L, M, & N will have the same number of rows (no blanks in cells)
> > > > 6.  Col P,Q,R,S & T will have the same number of rows (no blanks in cells)
> > > > 
> > > > · I would like to go to the last cell with data of each column individually 
> > > > (E, I, N, &  Q) and put the totals on the line below. (totals will not be on 
> > > > the same row).  
> > > > ·   Also as an alternative, I may like the totals all on the same row (find 
> > > > the last cell with data, go to the next row below and total those columns all 
> > > > on the same row.
> > > > ·  Whew, I think that’s about all.  
> > > > Thanks for trying to solve this.
> > > > John
0
Utf
6/6/2010 2:15:59 AM
Reply:

Similar Artilces:

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

Moving a value in Column D
I have a spreadsheet with values in a couple of cells in Column E. The rest of the cells are blank. I would like a macro that would move the value of any cell containing data (non empty cells) four cells backwards and two cells downwards to Column A. Is there a macro that would do this. -- Message posted via http://www.officekb.com Sub MoveIt() Dim rCell As Range For Each rCell In Columns("E") _ .SpecialCells(xlCellTypeConstants) rCell.Offset(2, -4).Value = rCell.Value rCell.ClearContents Next rCell End Sub HTH -- AP "mohd21uk via OfficeKB.com" <u20517@u...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

Re: fuction to copy Column a and b to comment or colum c?
Please help!!! I would like to know a fuction or a way to just recop what ever is in colum a and c to comment in colum d. Is this eve possible? If not maybe a fuction to put values of colum a and c t colum d. Please keep in mind this would prevent me from typing again. Thanks!!! -- Message posted from http://www.ExcelForum.com You can concatenate the values in A & C in column D with a formula: =A1&C1 or =A1&" "&C1 if you want those values in a comment, you'll need a little code. This is a sub (not a function): Option Explicit Sub MakeComments() Dim...

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

macro
I have several columns in an income sheet. One column has a sum at the bottom. I want to be able to run a simple macro that will copy the last row of formatted cells, just above the sum total, and insert it as a new row just above the sum total so another line of data can be input. But I want the sum total to sum also the newly inserted row. Presently I can't figure out how to do it. Example A B C 1 24 Oct Dollars In $100 2 25 Oct Dollars out $25 3 Total ...

How to make chart columns thicker
Hi I am using excel 2007. I am trying to create a chart with 20 weeks in the x axis. The chart type is Column, clustered column. The chart displays two lots of data. The problem is, the columns are displayed as very thin, in fact they're little more than lines, yet there is a lot of room on the chart for them to be much thicker. How do I make the columns thicker? The chart just looks daft at the moment. Thanks for any help Colin Ok, brain in gear now, I've worked out what was wrong. The problem was that the x-axes was recognising the data as a date. I told it that it was text by ...

Printing Long Columns
I have a simple worksheet with order numbers in column A and corresponding completion dates in column B. Since there are over 1,000 orders, it takes many pages to print the sheet, with a great deal of paper wasted on the blank area to the right of the two columns. Other than the tedious process of cutting and pasting, is there a way to arrange the information into multiple multiple columns across the page? Try this example on David's site Charlie http://www.mvps.org/dmcritchie/excel/snakecol.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Cha...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

Stacked Column Chart Data Label Connector Lines
I can't for the life of me figure out how to place the data labels for a stacked column chart (# or %) (1) next to the column, then (2) have lines connecting the data value to the proper block of the column. It does it automatically for pie chart labels, but not columns. Anyone have insight into these two issues? Thanks in advance. Not a solution, but explaination: XL won't let you place the labels by design to the side because its possible to place stacked columns next to each other (gap width = 0). This would then make it look like the values were associated with wrong col...

Total Items
Hi - Is there a way to get a total number of items that are about to be tendered, on the POS screen ? Thanks. You can customize the status.htm file. Not sure exactly what the variable is that you want, but I know it cn be done. "NJS" <NJS@discussions.microsoft.com> wrote in message news:538F3CC7-F23D-43C7-8A5A-4319B8688E5B@microsoft.com... > Hi - > Is there a way to get a total number of items that are about to be > tendered, > on the POS screen ? > Thanks. Jason, Send me your email I have already customized this and will forward you. "Jason&qu...

HOW TO SORT 4 COLUMNS
I HAVE A CUSTOMER LIST THAT I NEED TO SORT IN THIS ORDER, ST (COLUMN G), CITY (F), ZIP (H) & NAME (A). RANGE IS A6:M565. THANKS. VICTOR select data sort and pick the rows you want to sort by, If you want it in VBA then use your macro recorder, but before you record the macro practice on it first, there is nothing more maddenning than having to record he macro 5 or 6 times because of forgetting something. then from the forms button select a command button and assign a macro to it I should explain that I need to sort the criteria in those column and it only allows three. Consequentl...

Formulas sometimes will not work in a Column or cell?
I am perplexed a bit with an annoying problem that is user error I am sure: Most of the time, I can go to a cell and type in =F8+G8 and I get wonderful number that comes out. But other times the output will be "=F8+G8" and the whole column is that way? I think that this is because the column is text format.. so I select the column and format for Numeric- but nothing happens I still get "=F8+G8"- What can I do to format that column so I can do some formulas? Thanks brent You're correct about the cell being formatted as text. But it's not enough to just chang...

Total of Totals
Hey there, I've set up a database consisting of four tables. I run a query which calculates a total for the item (unit price * quantity). Is it possible to calculate the final total of all this for a report? If that's possible, how can I get it to be at the bottom of all the records but only on the last page? Thanks Mathew On Aug 1, 12:48 am, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Yes. > Open the form in design view, click on menu VIEW - Report Header/Footer a= nd > add a text box in the footer with Control Source =3D[unit price] * > [quantit...

How to change the individual row into columns
In Excel If each row looks like the below given format Michael 20, Eastman Avenue Boston US David 56, George Patel Road Texas US Michael 20, Eastman Avenue Boston US David 56, George Patel Road Texas US Michael 20, Eastman Avenue Boston US David 56, George Patel Road Texas US I want to change it to be the following format (Given below). Is i possible to do that? If so, what are the steps to be followed? I nee your help. Please reply this mail. Thanks in Advance. Michael 20, Eastman Avenue Boston US David 56, George Patel Road, Texas US Michael 20, Eastman Av...

Macro/SUB to merge two columns of text
EXCEL 2003: How do I write a VBA subroutine to: (1) Take each cell in the currently-SELECTED column (2) Append to its (text) contents the text in the corresponding cell of the NEXT column (3) Replace the result in the original cell (4) On completion, delete the whole of the NEXT column? All text values need to have leading and trailing blanks TRIMMED before being concatenated. This is to cope with the situation where downloaded data contains information split over two columns that the worksheet needs to have in one, e.g. company name and branch location. Sub m...

Manipulating totals and columns in Pivot Tables
Hi, I'm constructing a pivot table that has investors as columns, investments as rows, and the general data is values. Trouble is there is a column which I want to only display half of each value. I know that I can make a formula that divides a column "column A/2", and then hide the unwanted columns. But I would also like the hidden columns to not be included in the grand totals. How do I make this happen? Thanks! Hi, So let me understand what you want, something like this in the Pivot Table: A B C Name Jp ...

Pole display Sub Total
Hi All, When a client of mine scans items the amount displaying is the sub total. Is there any way to display the total (ie including tax amount) Regards Amanda ...

Print Visio columns?
...

how do I get a total for all worksheets in my workbook?
I have ten worksheets, each with a total. How do I get a total of all totals? Considering you have 3 sheets, and the totals are in cell A1, then use: =SUM(Sheet1:Sheet3!A1) Mangesh "Aileen Hewat" <AileenHewat@discussions.microsoft.com> wrote in message news:18C80118-642F-4690-8B5E-7D68B63752E3@microsoft.com... > I have ten worksheets, each with a total. How do I get a total of all totals? This file might be a help: http://www.bygsoftware.com/examples/zipfiles/consol.zip It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/exa...

Importing CSV file into single column/multiple rows vs. multiple rows/single column
Hello, I need to know how to import data into a single column with multiple rows. By default, when importing a CSV or TAB txt file, it imorts data across multiple columns in one row but I need it to go into one column and multiple rows. I don't have the option on the import wizard. Thanks, Brian Brian, Open it as usual, then do a copy pastespecial transpose. HTH, Bernie MS Excel MVP <bjorgenson@charter.net> wrote in message news:1119877803.916313.176730@g14g2000cwa.googlegroups.com... > Hello, I need to know how to import data into a single column with > multiple rows. B...

Setting column labels to letters
I just noticed that my worksheet column labels (across the top) are numbers instead of letters. Don't think it's always been that way, but I want to change it now and haven't found anything yet in Tools or Help that references it. Would someone please point the way. thanks, Gene Goldenfeld Gene, Go to the Tools menu, choose Options, then the General tab. There, uncheck the "R1C1 reference style" setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Gene Goldenfeld" <genegold@highstream.net>...

Calculate Total Books
I am preparing packing slips for boxes to be sent out. There are different book orders going into the same box. Each book in the order will have its own set of id numbers that are usually, but not always, in a string(101-121 etc). I use a calculated field on the form to display the number of books in the string as I store the start book number and subract it from the end book number and add 1 to get the actual number of books in the string. I use the same calculated field on the report to show the number of books in each detial, but I have multiple strings in 1 box. I need to show a...

Using fill with increasing columns.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel In one sheet (sheet 2) of my document I have a list that spans multiple columns. In the other sheet (sheet 1) the list is in multiple rows in the same column. <br> I want: <br> a1 of sheet 1 to equal a1 of sheet 2 <br> a2 of sheet 1 to equal b1 of sheet 2 <br> a3 of sheet 1 to equal c1 of sheet 2 <br> etc. <br> I can do this easily enough manually, but I cannot figure out how to do this using the fill function. The current function I am using is: <br> a1='sh...