On Tuesday March 06 2007 11:37 pm, Dwight Brown wrote:
> Dan Lewis wrote:
> > On Monday March 05 2007 9:44 pm, Dwight Brown wrote:
> >> -----Original Message-----
> >> From: [EMAIL PROTECTED]
> >> To: [email protected]
> >> Sent: Sun, 4 Mar 2007 10:31 PM
> >> Subject: [users] [moderated]only 30 numbers can be added in a
> >> column? Any fix?
> >>
> >> Is there any way to get around the apparent limitation of only
> >> 30 numbers can be added in a column?
> >>
> >> Later - I got one response which claims I can add as many
> >> numbers as I wish. Since I was not able to do this I called up
> >> "sum" in NeoOffice: SUM
> >> Adds all the numbers in a range of cells.
> >> Syntax:
> >> SUM(number1; number 2; ...; number 30)
> >> Number 1 to number 30 are up to 30 arguments whose sum is to be
> >> calculated. Example:
> >> If you enter the numbers 2; 3 and 4 in the Number 1; 2 and 3
> >> text boxes, 9 will be returned as the result.
> >> SUM(A1;A3;B5) calculates the sum of the three cells. SUM
> >> (A1:E10) calculates the sum of all cells in the A1 to E10 cell
> >> range. Conditions linked by AND can be used with the function
> >> SUM() in the following manner:
> >> Example assumption: You have entered invoices into a table.
> >> Column A contains the date value of the invoice, column B the
> >> amounts. You want to find a formula that you can use to return
> >> the total of all amounts only for a specific month, e.g. only
> >> the amount for the period >=1.1.99 to <1.2.99. The range with
> >> the date values covers A1:A40, the range containing the amounts
> >> to be totaled is B1:B40. C1 contains the start date, 1.1.99, of
> >> the invoices to be included and C2 the date, 1.2.99, that is no
> >> longer included.
> >> Enter the following formula as an array formula:
> >> =SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)
> >> In order to enter this as an array formula, you must press the
> >> Shift + Ctrl + Enter key instead of simply pressing the Enter
> >> key to close the formula. The formula will then be shown in the
> >> Formula bar enclosed in braces.
> >> {=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)}
> >> The formula is based on the fact that the result of a comparison
> >> is 1, if the criterion is met and 0 if it is not. The individual
> >> comparison results will be treated as an array and used in
> >> matrix multiplication, and at the end the individual values will
> >> be totaled to give the result matrix. The SUM() function can
> >> also be used in this way, for example, as COUNTIF() with several
> >> criteria.
> >>
> >> I looked also at Open Office for the Sum function but found the
> >> copy function very unstable. It would not show the whole
> >> article, identical to the one above from NeoOffice and it would
> >> not copy at all. In any case, my work around for this is to
> >> select each cell I want to include in he total and at the bottom
> >> of the screen there is a Total box which actually gives me the
> >> total of every box I selected, but I have figured out how to get
> >> this in a box at thebottom of he column. I realize we are
> >> inbeta on this program, and I do not know whether my experience
> >> is peculiar to my machine (a nerw iMac, Intel) but thought I
> >> would pass it along. Dwight Brown
> >
> > I am thinking that you have not completely grasped the SUM()
> > function. There is a limit to the number of entries you can place
> > into the SUM() function: 30. But this is 30 individual cells or
> > cell ranges. You can use the SUM() function to add a single range
> > of cells that consistes of many more cells than 30. For example,
> > I have a list of donations made to our cemetery fund at our
> > church. There are 166 entried in cells E2 through E167. The sum
> > is calculated by this formula: =SUM(E2:E167). Notice that these
> > is only "one" entry" in this case.
> >
> > Dan
>
> Thanks for this reply. When I have a contiguous list of numbers I
> can add them OK but I am working with columns in a bookkeeping
> spreadsheet and there may be 15 or 20 numbers in a system which has
> over 200 cells in a column, most of them empty. The sum function is
> drawn to the adjacent column, the first column I am trying to add,
> and I cannot get the program to look up for 10 or more blanks cells
> to find the occasional cell with a value in it - I selected every
> way I could think of, and kept getting cell references to the first
> column I added. Finally I figured out that if I simply drop the
> cursor on any cell in the column which has a value, I get a line at
> the bottom, not in the spreadsheet, which indeed sums up the cells
> on which I had dropped the cursor. Trouble is, I could not figure
> out how to get this number up into the spreadsheet(except just copy
> is by hand,which is subject to error) to form a row of column
> totals which could then be added by the same method (drop cursor on
> cell with value) in order to confirm that the total entries in the
> columns equaled the balance given by the bank. I believe, in short,
> that the sum function has bias to look for the nearest number, and
> in addition has a problem with adding non-sequential collections of
> numbers, where there are large gaps between one number and the
> next one..
> I would be willing to send this spreadsheet, even though it has my
> personal financial information for a month, if you would like to
> experiment with it directly. Perhaps you could determine either
> some better way for me to get the results I need, OR you could get
> credit for reporting a legitimate had shelled animal.
> Dwight Brown
Here is an idea. Suppose column E has 15 entries scattered over
200 rows (row numbers are from 4 to 203, and you want to add the
entries in cell E204. Enter this formula into cell E204:
=SUM(E4:E203)
It will add all of the entries and display the total.
If you wanted the total to appear in cell E1 instead of E203
place this same formula in E1 instead of E203.
Furthermore, if you want column totals for columns F and G as
well as E, there is a trick to doing that also. Once you have entered
the formula in the cell you want, click the cell (E204 for example).
A little square appears at the bottom right corner of the cell. It is
called a handle. Click and drag it to the right so that E204, F204,
andG204 are highlighted together. Release the mouse button. All three
columns now have their column totals in row 204.
I also have a personal finance Calc file with many column totals
as well as row totals. I use this method to enter my SUM() function
with its values into one column or row and then drag it until I get
all of the other totals I want in the other rows and columns.
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
