Moving Column Sums to Rows

What is the simplest way to enter a sequence of values such as =SUM 
(A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN?

What is the simplest way of converting simultaneously such values to 
($a$1:$a$100) etc.?

I could only find a slow and clumsy method by putting the formula in a row, 
right dragging, then using F4 on each individual cell before using paste 
special with transpose.

Roger PB 


0
3/23/2005 7:38:56 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
363 Views

Similar Articles

[PageSpeed] 33

Hi Roger,

> What is the simplest way to enter a sequence of values such as =SUM 
> (A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN?
>

=SUM(OFFSET($A$1,0,ROW()-1,1,100))

Assuming you start on row 1. If starting on e.g. row 2:

=SUM(OFFSET($A$1,0,ROW()-2,1,100))

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

0
jkpieterse (271)
3/23/2005 8:43:45 AM
Roger

For this you can use VBA macro(s)

Here are 4 macros.  Use whichever you deem appropriate.

For your example, Sub Absolute() would be the one.

Sub Absolute()
Dim Cell As Range
    For Each Cell In Selection
        If Cell.HasFormula Then
            Cell.Formula = Application.ConvertFormula(Cell.Formula, _
            xlA1, xlA1, xlAbsolute)
        End If
    Next
End Sub

Sub AbsoluteRow()
Dim Cell As Range
    For Each Cell In Selection
        If Cell.HasFormula Then
            Cell.Formula = Application.ConvertFormula(Cell.Formula, _
            xlA1, xlA1, xlAbsRowRelColumn)
        Next
    End Sub

Sub AbsoluteCol()
Dim Cell As Range
    For Each Cell In Selection
        If Cell.HasFormula Then
            Cell.Formula = Application.ConvertFormula(Cell.Formula, _
            xlA1, xlA1, xlRelRowAbsColumn)
        Next
    End Sub

Sub Relative()
Dim Cell As Range
    For Each Cell In Selection
        If Cell.HasFormula Then
            Cell.Formula = Application.ConvertFormula(Cell.Formula, _
            xlA1, xlA1, xlRelative)
        Next
    End Sub


Gord Dibben Excel MVP

On Wed, 23 Mar 2005 07:38:56 GMT, "Roger PB" <translator@vienna.at> wrote:

>What is the simplest way of converting simultaneously such values to 
>($a$1:$a$100) etc.?
>
>I could only find a slow and clumsy method by putting the formula in a row, 
>right dragging, then using F4 on each individual cell before using paste 
>special with transpose.
>
>Roger PB 

0
Gord
3/23/2005 5:59:41 PM
Oh dear, is there nothing simpler?

I tried  Jan Karel's solution.But first I ran into the problem that for some 
reason my version of Excel demands German syntax, i.e. it does not 
understand commas and demands semi.colons. Similarly, it demands dates to be 
formatted  tt, mm, jj, rather than dd, mm,yy, and to date I have found no 
way to change this. However, the help screens use English syntax!

Anyway,having entered the modified formula, when I dragged it down the 
column, it simply repeated itself, and did not adjust for  b, c, etc.

I am a beginner as regards VBA programming, which is why I sent my query to 
this newsgroup rather than the excel programming group. But Gord Dibben's 
solutions I find useful ,especially as the syntax regarding changing 
absolute to relative formulas or vice versa.


0
3/23/2005 10:04:35 PM
Hi Roger,

> I tried  Jan Karel's solution.But first I ran into the problem that for some 
> reason my version of Excel demands German syntax, i.e. it does not 
> understand commas and demands semi.colons.

I know about the semicolons, I have to use them too (Dutch settings). I simply 
assumed English in this case.

There is a number of translation tools to translate functions from English to 
German, one of them is simply a list. Find xlMenufundict.zip at:

www.jkp-ads.com/Download.htmxlMenufundict

> Similarly, it demands dates to be 
> formatted  tt, mm, jj, rather than dd, mm,yy, and to date I have found no 
> way to change this. However, the help screens use English syntax!

What does this have to do with your original query?
Excel uses the dat starting letters of your local language setting on Regional 
settings for the formatting string. So if you want them to be english, you need 
to set your regional settings to english.

> Anyway,having entered the modified formula, when I dragged it down the 
> column, it simply repeated itself, and did not adjust for  b, c, etc.

But it should POINT to the right ranges, the $A$1 part SHOULD not change, it is 
the ROW part that adjusts the range.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

0
jkpieterse (271)
3/24/2005 7:35:57 AM
Hi Jan Karel,

Manuy thanks for pointing me in the right direction. And for 
Findxlmenufundict. zip.

The dates problem relates to my original query in that both the German 
syntax and ttddjj settings stemmed from the regional settings. I have 
succeeded in changing these to English, thanks to your tip.

I have now also got the formula to work: The top row entry reads 
=SUM(OFFSET($A$1,0,ROW()-1,27,1))
and sums A1:A27. I found it rather odd that the formula looks identical 
fthroughout the column, but it works.

 In the Help menu I found the syntax 
OFFSET(reference,rows,cols,height,width)
and two examples
      =OFFSET(C3,2,3,1,1) Displays the value in cell F5 (0)
      =SUM(OFFSET(C3:E5,-1,0,3,3)) Sums the range C2:E4 (0)

But nowhere could I find what ROW() signifies.

Does $A$1,0,ROW(),represents A1 with no row offset- the zero value- and a 
column offset of ROW()-1?
If this is equal to 0, am I right in assuming that ROW() has the same value 
as the row that it is placed in, i.e. 1 in row 1, 2 in row 2 etc ?

Many thanks for your patience and help.

Roger PB


0
3/25/2005 9:28:13 PM
Hi Roger,

> But nowhere could I find what ROW() signifies
>

ROW() returns the rownumber of the cell the function resides in, so 
=ROW()

in Cell A63 returns 63.

Since I used the ROW() function as the column argument for the offset, 
dragging the formula down forces an increase in the column argument of 
the offset function. SO in fact when you move down one row, the formula 
in that row points one column further to the right than the one 
immediately above.

Clear as mud?

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

0
jkpieterse (271)
3/26/2005 3:09:59 PM
Reply:

Similar Artilces:

Counting the number of fields in a column that have data?
I have an Excel document with about 5,300 rows. One of the columns has data entered only into some of the fields, maybe 100 of the 5,300 rows. Is there an easy way to get Excel to count all of the fields in that column that have data entered in it? Thanks! On 1/25/10 4:57 PM, in article eW3gilgnKHA.1548@TK2MSFTNGP02.phx.gbl, "Rick" <someone@yahoo.com> wrote: > I have an Excel document with about 5,300 rows. One of the columns has > data entered only into some of the fields, maybe 100 of the 5,300 rows. > > Is there an easy way to get Excel to cou...

Help with auto height of rows
Hello, I've got Excel 2002. Spreadsheet has over 3400 rows and 6 columns. No merged cells. All fonts are Arial size 10. The first 1809 rows are set to a height of '15.00 (20 pixels)' and the remaining rows are set to '12.75 (17 pixels)'. I've tried selecting all rows and then double clicking on a divider bar to get them all to adjust to auto height, but they stay at the heights listed. I want them all to be at the 12.75 (17 pixels) height when you do the auto height adjustment. Why won't this work on this spreadsheet? Thanks. Mark Nevermind - I fig...

Increasing # of rows in excel sheet
Hi all.. I usually import data from other programs such as SAP into excel and face the problem of excel running out of Rows.. is there a way that I can increase the total number of rows in my worksheet or any other solution possible to my problem. Thanks Rehan Hi no. 65536 rows is the maximum "Rehan" wrote: > Hi all.. > > I usually import data from other programs such as SAP > into excel and face the problem of excel running out of > Rows.. is there a way that I can increase the total > number of rows in my worksheet or any other solution > possibl...

Copying a column from several similar sheets into one sheet
I have several worksheets in one workbook and I want to merge/copy one column from each worksheet (the same column) (Like this, just imagine more columns with more data on several worksheets Date 06052003 Actual 13 FMT 12 % 87 ) and put these columns into rows so that each worksheet's data will descend vertically (EX: Date Actual FMT % 06052003 13 12 87 06062003 12 15 89 06072003 11 17 90 That's what I want, Please help me to do it. ...

pst file move
I use office xp with windows xp. I would like to move my outlook.pst file to another hard disk because the old one is full. How can I do it? The following article should provide the details you need for this request: 287070 OL2002: How to Back Up, Restore, or Move Outlook Data http://support.microsoft.com/?id=287070 -- Hope that helps!! Thank you! Sincerely, Chris Lineback, MCSE Microsoft Enterprise Messaging Support - Please do not send email directly to this alias. This alias is for newsgroup purposes only. -This posting is provided "AS IS" with no warranties, and confers...

Move Mailbox with source Server Offline
I have moved one of my E5.5 servers from Production into a Test lab to test E5.5 to E2K3 migration scenarios. I imported a full GAL export from Production just to make sure that all Users, Custom Recipients, and Distribution Lists came over OK. Now, however, I have Mailboxes that think they are on servers that don't exist in the lab! I need to find a way (import, vbscript, etc.) that can tell the mailbox that it's Home server is the lab server; even though the Home server it's currently set to doesn't exist in this network. Any ideas? -Rick Adams SORRY! PLEASE D...

Sql Server Indexing With Two or More Columns
I got a question with indexing. If I create an index and select 2 or more columns, what is the difference with that and creating 2 (or more separate ) indexes for them? Thanks mark It depends on what you are doing If you have WHERE Last=@p1 AND First=@p2 there no need to have two indexes , however having WHERE First=@p2 the first index on Last,First may or may not be useful, so in that case having two NCI may be a good idea I mean you need testing it, and make a decision.Also there is no need to create NCI on every column, it is especially true in SQL Server 2005 and onwards w...

Adding a calculated ROW to a pivot table
Does anybody know how to add a calculated ROW to a pivot table? I have a pivot table that is returning totals at the bottom, as it should, but I *also* need it to return that total as a percentage of grand total, directly beneath the total. I've always done this free-form in the cells below a pivot table before, but the size of this pivot is dynamic so that's not an option. Also--I'm using the pivot in Access, not Excel directly. Anybody have any tips? Thanks! ...

how do I remove Carriage Returns from a column in a spreadsheet?
I have some data that I am manipulating in Excel to do an export to another application. I need to be able to do a universal find and replace to get rid of instances of Carriage Returns and Line Feeds within the data. I have tried the basic find and replace functions but I don't know how to make them find a carriage return character. Any help would be greatly apreciated. Doug You could use a 'helper' column and the SUBSTITUTE function This one replaces line feeds with nothing =SUBSTITUTE(C2,CHAR(10),"") Use CHAR(13) for carriage returns You can then Edit>P...

Form creates unwanted rows in table
I've created an asset database which keeps track of computers, laptops etc. There is one company which has many departments and every department has many rooms. (surprise, surprise) There are four tables: tables Department and Asset and two "middle" tables DepartmentRoom and AssetRoom. The table structures are as follows: DEPARTMENT id name dep_type dep_num etc... ASSET id name a_type ip_num vendorID serial_num etc... DEPARTMENTROOM id name depID (foreign key to department) ASSETROOM id assetID (foreign key to asset) roomID (foreign key to depa...

Rows to repeat getting cut off on 2nd page
I set rows 1-3 to repeat on all pages, but on the second page, it is getting cut off a few columns in.any suggestions? thanks! ...

Sums and percentages in reports
I have the following field in report's detail area: =Count([Cause]=1) (where 1 refers to a row of a table's dropdown) at the report footer I have the following sum for that column: =Sum(Abs([Cause]=1)) with the Runnin Sum - Over All. The calculation comes out correc so I'm assuming the formulas are correct. However, when I try to get a Percentage by deviding the field formula with the footer formula I don't get the correct answer. Can someone help me with this formula? You have told us what you attempted but it isn't clear what you want to accomplish. Your expres...

comparing cells in one column to another
I have a column on a spreadsheet and an array of strings. I need to look at the first cell on the spreadsheet and find a matching value in the array of strings. This is how I am doing that With ThisWorkbook.Worksheets(Tracker).Range("Order_Number_Header") For E = 0 To Total_Rows_On_Spreadsheet - 1 For F = 0 To Total_Elements_In_Array - 1 If .Offset(E, 0) = FTS_Order_Number(F) Then msgbox"I Have a Match end if next F next E end with Nothing is matching and I know there are some matches. When I display the value in the fields I see "12345678" for both fields. The f...

Summing numbers between a date range
I need to sum values in a row of data where the date in the column of the row falls within a certain date range. Here is an example of the data. The data to be summed is in sheet one and the dates used for the criteria are in sheet 2. Sheet_1: col a col b col c col d Nov 15/92 Feb 15/93 Sep 15/93 Feb 15/94 15.00 20.00 30.00 10.00 Sheet_2: col a col b Jan 1/93 Dec 31/93 Jan 2/94 Dec 31/94 I basically want to sum the data in Sheet_1 that is between a date range based on Sheet_2. For example, sum values in sheet_...

Pivot table calculated field: multiply Sum of FieldA with Max of FieldB
Hello usenetters, My question concerns Excel 2003. A user asked me about a calculated field in a pivot table. Some of the fields are DT_NOW, GW_WCR, GW_LOB. The formula of the calculated field should depend on the date (DT_NOW): Up to 30/06/2009: Rotation = GW_WCR / GW_LOB * 91 Starting 01/07/2009: Rotation = GW_WCR / GW_LOB * 28 She added a field to the underlying data called ROT_FACTOR, filled with 91 until 30th june and filled with 28 starting 1st july. The current formula is =IF(ROT_FACTOR>300000;GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28) The cutoff value 300000 is an arbitrary value base...

how do i get missing rows back
My excel worksheet is missing rows 37-58. There is a heavy gray gridline in between rows 36 & 59. Where did they go? How do I get them back? Hi Select from rows 36:59>right click>Unhide -- Regards Roger Govier "dkresch" <dkresch@discussions.microsoft.com> wrote in message news:0EDE73AB-87DB-43DB-B9DB-E7E82C7D39D9@microsoft.com... > My excel worksheet is missing rows 37-58. There is a heavy gray gridline > in > between rows 36 & 59. Where did they go? How do I get them back? "dkresch" <dkresch@discussions.microsoft.com> wro...

importing columns
How can all the values from one column without putting the individual lookup in it? I want B3:B53 to be the same on 2 different worksheets, and only have to enter the data once. one way is to put =source!b3 in a cell on the destination sheet and copy down -- Don Guillett SalesAid Software donaldb@281.com "ANTiSEEN" <lakepir8@tampabay.rr.kom> wrote in message news:pBMEe.15413$iG6.9509@tornado.tampabay.rr.com... > How can all the values from one column without putting the individual lookup > in it? > > I want B3:B53 to be the same on 2 different worksheets, a...

Possible to "rotate" range of cells so columns are rows and vice versa?
Is it possible to select a rectangular range of cells and then generate a copied range of cells that is a rotation of the first range, where the columns are the rows, and vice versa? Maybe copy, then paste special and select transpose Regards, Peo Sjoblom davidmichaelkarr@gmail.com wrote: > Is it possible to select a rectangular range of cells and then generate > a copied range of cells that is a rotation of the first range, where > the columns are the rows, and vice versa? > Never mind, I discovered "Paste Special" and "Transpose". ...

transpose 3d cells to a column in single workbook
I would like to extract 3d cells from a set of worksheets into a single column on another worksheet within the same workbook. How can I do this? Insert a worksheet named Summary, then select the 3D range, and run the macro below. HTH, Bernie MS Excel MVP Sub TryNow() Dim mySht As Worksheet Dim dataSht As Worksheet Dim myRange As Range Dim myCell As Range Set dataSht = Worksheets("Summary") Set myRange = Selection For Each mySht In ActiveWindow.SelectedSheets For Each myCell In mySht.Range(myRange.Address) dataSht.Range("A65536").End(xlUp)(2).Value = _ myCell.Value N...

Can't move mailbox
My question is similar to "Can't Purge Mailbox". I have one mailbox left to move in a migration from exchange 2000 to 2003, and it will not move. I can move it between 2000 stores and servers but not to exchange 2003. I recieve the following error when I try to move it: - <summary isWarning="false" errorCode="0xc1054005"> The MAPI call failed. MAPI or an unspecified service provider. ID no: 80004005-0000-00000000 There are no corrupted messages, I have checked each one. The mailbox size is under 200mb. This is the only mailbox left to move??? I ...

% as column not Pie
Im trying to create a single column chart to express three values as a percentage of the overall total. It works using the default pie chart but I cant get it to express the values in a vertical single column. Thanks for any tips or help in advance. In the chart wizard, select the 100% Stacked Column chart type, the top left option of the Column chart family. If your three values are aligned horizontally in the sheet, make sure the chart is aligned by columns (or vice versa). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions ht...

Moving shape just a smidgen
I am using an old version of Visio Professional (5.0) and am trying to get a shape to move just a hair as the connection line isn't exactly straight. If I move it just a little, the line goes to far and is not straight in the other direction. I can't use the arrows as they just move the page up and down. Thanks, Tom. I can't remember if this worked in 5 or not but did you try holding the ctrl or shift key while using the arrows? -- Shaun Beane, MCT, MCDBA http://dbageek.blogspot.com "Thomas Scheiderich" <tfs@deltanet.com> wrote in message news:10ki8i957...

conditional formatting:highlight row based on blank or non-blank c
Does anyone know if, and how, is possible to use Conditional Formatting feature to automatically highlight the whole row if a specific cell in that row is non-blank (or blank)? Thank you! Hi, Yes, first select your row then in your conditional formatting select "formula is" from the drop down menu and type: =ISBLANK($A1)=FALSE or =ISBLANK($A1)=TRUE depending on weather you want the condition to apply when your cell is blank or non blank. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum....

Count unique values for rows meeting specific criteria?
Been going around in circles with this for a while.. part way there, but not completely.. I need to COUNT the number of UNIQUE values in Column A, but only count rows if columns Q and R meet a certain criteria. I have used this formula for the total unique values: =(SUM(IF(FREQUENCY($A$2:$A$5507,$A$2:$A$5507)>0,1))) Now I would like to know if I can use this formula, or if I need to use something else to get my answer. The two criteria I need to be met before the row can be included in the unique value calculation are: ($Q$2:$Q$5507>=DATE(1998,6,31)) AND ($R$2:$R$5507<=DATE(1...

New email moved in Deleted items folder
Hi, After we activated a rule to transfert new e-mail to another mailbox all new e-mail were automatically moved to the deleted items folder. We had used this rule before with no problem. I first deactivate the rule and e-mail are still going to deleted items. I've also tried to delete the rule but still have the problem. It's an Exchange 2007 user mailbox and we are using Outlook 2007 on client side. Line Open Outlook with the /cleanrules switch to deactivate all rules both client and server side. You may have rules in conflict. "LM" <LM@discussio...