Sum numbers in a column beyond a certain row, how to?

How to sum numbers in a column beyond a certain row?
0
arasu.kay (2)
2/15/2012 6:25:22 AM
excel 39879 articles. 2 followers. Follow

6 Replies
918 Views

Similar Articles

[PageSpeed] 11

hi,

i don't know if i understand your question correctly,

=Sum(A1:A10)


-- 
isabelle



Le 2012-02-15 01:25, marsup a �crit :
> How to sum numbers in a column beyond a certain row?
0
isabelle
2/17/2012 12:30:59 AM
On Feb 17, 11:30=A0am, isabelle <i...@v.org> wrote:
> hi,
>
> i don't know if i understand your question correctly,
>
> =3DSum(A1:A10)
>
> --
> isabelle
>
> Le 2012-02-15 01:25,marsupa =E9crit :
>
>
>
>
>
>
>
> > How to sum numbers in a column beyond a certain row?

Thanks Isabelle, I am not looking to sum A1:A10, I wish to sum all
numbers say from A10 to beyond like A11, A12.. A13
as I keep adding as update. Hope I made this clear this time. Thanks.

Marsup
0
arasu.kay (2)
2/19/2012 3:51:30 AM
ok i better understand, the last row filled in column "A" may be determined by the array formula
MAX(IF(A1:A65535<>"",ROW(A1:A65535)))
then to sum :

=SUM(INDIRECT("A1:A"&MAX(IF(A1:A65535<>"",ROW(A1:A65535)))))
array formula ---> validate with  ctrl + maj + enter

-- 
isabelle



Le 2012-02-18 22:51, marsup a �crit :
> On Feb 17, 11:30 am, isabelle<i...@v.org>  wrote:
>> hi,
>>
>> i don't know if i understand your question correctly,
>>
>> =Sum(A1:A10)
>>
>> --
>> isabelle
>>
>> Le 2012-02-15 01:25,marsupa �crit
>>> How to sum numbers in a column beyond a certain row?
>
> Thanks Isabelle, I am not looking to sum A1:A10, I wish to sum all
> numbers say from A10 to beyond like A11, A12.. A13
> as I keep adding as update. Hope I made this clear this time. Thanks.
>
> Marsup
0
isabelle
2/20/2012 1:43:30 AM
sorry,

validate with  ctrl + shift + enter

-- 
isabelle



Le 2012-02-19 20:43, isabelle a �crit :
> ok i better understand, the last row filled in column "A" may be determined by the array formula
> MAX(IF(A1:A65535<>"",ROW(A1:A65535)))
> then to sum :
>
> =SUM(INDIRECT("A1:A"&MAX(IF(A1:A65535<>"",ROW(A1:A65535)))))
> array formula ---> validate with ctrl + maj + enter
>
0
isabelle
2/20/2012 1:55:28 AM
also you can change

MAX(IF(A1:A65535<>"",ROW(A1:A65535)))
last value (text or number)

by,

=MAX(IF(ISNUMBER(A1:A65535),ROW(A1:A65535)))
last value (number)


or by,

=MAX(IF(ISTEXT(A1:A65535),ROW(A1:A65535)))
last value (text)



-- 
isabelle



Le 2012-02-19 20:55, isabelle a �crit :
> sorry,
>
> validate with ctrl + shift + enter
>
0
isabelle
2/20/2012 2:28:52 AM
=SUM(A:A)-SUM(A1:A10)

This will work whether you are using Excel 2003 or later versions
0
2/20/2012 2:06:16 PM
Reply:

Similar Artilces:

Converting a number to 2 decimal places
I would like to be able to type a number into a cell and have i automatically converted to a number with the last 2 numbers to th right of the decimal point. For example, if I typed in 1234, it woul become 12.34. How can I do that -- mattsla ----------------------------------------------------------------------- mattslav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2403 View this thread: http://www.excelforum.com/showthread.php?threadid=37648 Tools>Options>Edit Check Fixed decimal places Set for 2 Does that help? Ron -- Ron Coderre -------------...

format differnet widths columns and rows for different pages
I would like to know how you can have different formatting for row and colunm widths on different pages; is this possible and if so how do you do it?? You could use VBA to change the column.width for a worksheet..... if you were talking about that? -- Dnereb ------------------------------------------------------------------------ Dnereb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26182 View this thread: http://www.excelforum.com/showthread.php?threadid=486499 By "pages" do you mean printed pages on one worksheet? On each Excel worksheet th...

Remove Hyphens from a number
I have numbers in a coulmn that look like this: 01-012-4123 how do I remove only the hyphens (dashes) Select your range and Edit|Replace What: - (hyphen) with: (leave blank) You may want to format those cells (custom: 000000000) to keep the leading 0's. Mike wrote: > > I have numbers in a coulmn that look like this: > > 01-012-4123 how do I remove only the hyphens (dashes) -- Dave Peterson ec35720@msn.com Add a column with something like this in . =SUBSTITUTE(A1,"-","") Robin Hammond www.enhanceddatasystems.com "Mike" <anonymous...

Random Number Question
Does anyone have a Random Number Formula for creating the following Random Numbers in 52 different cells with no number repeated Yes I am working on a Poker Rally and need to generate score cards that are different and Random Steve Boyle By the way thanks for the help on my last question. If I ever remember to take the print out to work and try the answers I will let you know how well it worked. Steve, This should be exactly what you need. http://tinyurl.com/2jgd8d John Wilson "Steve Boyle" <waylandboyle@comcast.net> wrote in message news:ACDCCD1E-D8C6-4A1A-BCE5-...

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...

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." &...

Replace variable row number in formulas
What I'm looking for is a macro that will replace the row number in a formula with a new number that I designate through an input box. Below is the code I have thus far. The problem lies in how I'm defining the integer portion of the formula that I want to replace - vbInteger (or vbLong) don't seem to work. Then finally I need to set Section 2 in a loop through Column CN. Any advice would be greatly appreciated! --------------------------------------------------------------------------------------------------------------------------- Sub UpdateFormulas_2() Dim LRowNumber ...

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...

Inserting Incremental number
Hi there! I have a table of Assessors [TblAssessors] and a field [RegNo] which is filled in later when trainees have successfully passed. This is later used on a Certificate of Registration. As not all trainees will successfully complete, I'd like to create a macro that will assign an incremental [RegNo] on a button's click event on my data entry form "FrmAssessors". Unfortunately, I have inherited this data and [RegNo] is in the format of "QTA00101","QTA00102", etc. The prefix will always be "QTA" and at this stage we do not envisage eve...

Number formatting question
I have hundreds of rows of numbers (example 234553) it need to add a decimal to every row to make it 2345.53. Is this possible? Yes One method Put the value 100 in a blank cell. With that cell selected, Edit/Copy Select the numbers you wish to change (highlight the column) Use Edit/Paste Special and select the Divide option. HTH PC "sbhayes" <sbhayes@discussions.microsoft.com> wrote in message news:74C0C90C-6C93-4055-8983-430D97B09692@microsoft.com... > I have hundreds of rows of numbers (example 234553) it need to add a decimal > to every row to make it 2345.5...

Word Auto Number Question
I'm making tickets using a perforated business card sheet, and am wondering if there is a way to automatically number the individual tickets, i.e., not pages. Thanks! Kathryn Douglas There's a newsgroup called microsoft.public.word.numbering that you should check out for this. Also try microsoft.public.word.newusers. "Kathryn Douglas" <anonymous@discussions.microsoft.com> wrote in message news:0ca401c397e4$db5ba5a0$a401280a@phx.gbl... > I'm making tickets using a perforated business card sheet, > and am wondering if there is a way to automatically number...

How to Combine Data from Several Columns?
Hi Everyone I have attached a file which contains what I need to resolve Basically, I need to combine the data in several columns together int one column. Is there a way I can do it with formulas or vb code? would really appreciate if any experts out there can help me. Thanks and Regards Kelvi Attachment filename: book1.xls Download attachment: http://www.excelforum.com/attachment.php?postid=46105 -- Message posted from http://www.ExcelForum.com Hi Kelvin! Probably the answer will be something like: =A1&" "&B1&" "&C1 ...

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...

How do I change minus numbers to + numbers in a col in Excel?
Is there a way to highlight a column or a row and change all of the numbers from negative to positive or vice versa without doing the change cell by cell? Thanks "Paul L" <Paul L@discussions.microsoft.com> wrote in message news:74D3CE9E-0454-4AA5-89B8-3CA77BECA01B@microsoft.com... > Is there a way to highlight a column or a row and change all of the > numbers > from negative to positive or vice versa without doing the change cell by > cell? Thanks Put -1 in a spare cell, and Copy. Select the cells you want to change, then Paste Special/ Multiply -- David B...

Error number: 3326 04-02-10
I have this odd problem. When I run this function from within Access everything is fine. Public Function sendEmail(strTo As String, _ strCC As String, _ strBCC As String, _ strSubject As String, _ strMessageBody As String, _ strAttachments As String) As Boolean 'Arvin Meyer 03/12/1999 'Updated 7/21/2001 On Error GoTo Error_Handler Const errUserDeny As Long = 287 ' Un numéro d'erreur...

Deleting multiple rows from value list
Is there a way to delete multiple rows in a value list for a Text column? I have over 200 rows out of a value list that are duplicates that I need to delete. I can delete them row by row but I'd like to click on the 1st row and shift click on the last row that needs to deleted. Is this possible? Thanks in advance (from a Project newbie) Yes. Select then click on the cut icon. Try it. -Jack Dahlgren "Val" <Val@discussions.microsoft.com> wrote in message news:6596A51E-9C60-46A4-B9AB-AAB335A92906@microsoft.com... > Is there a way to delete multiple ro...

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 ...

Prevent sequential number being used if record not saved
I have an invoice form that when opened, uses the following code to allocate the next invoice number (InvNo): Private Sub Form_BeforeInsert(Cancel As Integer) Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1 Me.Dirty = False End Sub Trouble is, if I then decide for some reason not to carry on, even if I click on the Close button rightaway without having entered anything anywhere, the invoice number is still used and appears in the Invoices table (without any values in any of the fields, of course). How can I prevent this? Ideally I would like to be able to...

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...

=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...

Numbers #2
I am using Office 2003 and am trying to do an expense sheet for a new business .I am doing a sheet for each month and need to put numbers in first column for each day 1-30 and 1-31. I have used Excel in the past and remember highlighting the first three numbers and dragging the rest to complete but it's not working. HELP -- sleepydee You're close. Instead of dragging by clicking on the cell's outline, click on the little black square at the bottom right corner of the outline. Szalapski -- TommySzalapski ---------------------------------------------------------------...

Formatting footnotes when page numbering is turned on
When I have paragraph numbering turned on, Word insists on assigning a paragraph number to my footnotes as well as a footnote number. I can manually delete the paragraph number, but would prefer to turn off the option. More important, however, is that Word also assigns a paragraph number to the line drawn across the page above the footnotes, and I cannot find a way to remove this. Can somebody offer me a suggestion on what to do? It would appear that you have applied numbering to the Normal style, which affects every style based on it. This is one reason we advocate using Bod...

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 ...