SUM

  • Thread starter Thread starter Eileen
  • Start date Start date
E

Eileen

When I try to sum up a partial column that has blank cells, Excel locks up.
I then have to 'End Task' I have been using CTRL to select the cells that I
want to add. What am I doing wrong?
Thanks, Eileen
 
Hi Eileen

you didn't say how many cell blocks you were selecting - is there any chance
that you're selecting more than 30 as this could cause a problem.
 
I just counted them... 29 cells but there were a lot of empty cells that I
didn't use because of using the Ctrl key. If I had used the Shift
(highlighting all cells from 222 to 287) there would have been 65.
Thanks, Eileen
 
Hi Eileen

if you use the SHIFT key then they're counted as one parameter ... what the
issue is here is that the syntax of the SUM function is
SUM(number1,number2, ...)

where Number1, number2, ... are 1 to 30 arguments for which you want the
total value or sum.

so,
=SUM(A222:A230)
is only couned as one arguement, whereas
=SUM(A222,A223,A224,A226,A229,A230)
is counted as 6

so coming back to your original problem, you should be able to select up to
30 arguments when using the SUM function without problems, unless you're
exceeding the maximum number of characters allowed in a function (which is
1024 characters)

so let's try something else ... select the 29 cells you want to add using
the control key and click in the name box (little box to left of formula
bar), type a name (e.g. CCells) and press Enter
now in a cell type
=SUM(CCells)
and see if that works.
 
That means that either you didn't match the spelling of the named range in
the formula, or, you didn't really create that named range in the first
place.

Expand the name box by clicking on the little down arrow to the right of the
name box.
Does the spelling of the range match what's in the formula?

Do you even see the name of the range you created displayed in the drop-down
window?
If not, try again, following Julies directions exactly.

You can also try:
<Insert> <Name> <Define>
Where you should find the name of the range you created.
Click on the range name and you'll see the individual cells included in the
range displayed in the "Refers To" box.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Julie,
That didn't bring up a sum... what came up in the cell was "#NAME?".
Eileen
 
It worked this time. I have several columns to do on the same page. Do I
use a different name for each one?
Thanks, Eileen
 
Depends on exactly what you want to accomplish.

If you're *only* interested in a single total for all the cells, a single
named range would be easier to work with.
Keep in mind though, that there is a limit to the number of *individual*
cells that can be included in a range.
This limit is dependant on the number of characters it takes to describe the
entire range, as displayed in the "refers to" box of the <Insert> <Name>
<Define> window.
Confused enough?

I would create as few ranges as possible, if I was only looking for a single
total.
If you do end up with only one range, and it does contain a large number of
individual cells, manually check that the total is correct, because if XL
decides that the range description is too long, it will simply *exclude*
(truncate) the cells that surpassed the limit, without any notification to
you.
 
Is this correct.... I use 'ccell' to add a column. Then I go to another
column... I can use 'ccell' again?
Eileen
 
Hi Eileen

you need to select them all first then name them if you've using the name
box - so delete ccell (insert / name / define /delete)
select all your columns (select the first, hold down control, keep control
down, select the second, still keeping control down, select the third until
they're all selected)
now click in side the name box, type ccell and press enter
now try the formula
=SUM(ccell)
press enter
does it work now?
 
All the cells should be named at *one time*.

You can add to a named range by editing the "refers to" box, but I don't
want to make things too complicated for you.

Let's delete the existing name, and then, start over, and select *all* the
cells, and then rename them again.

<Insert> <Name> <Define>
Click on "ccell" in the window, then
<Delete>, and then <OK>.

Now, starting over, select *all* the cells that you wish to include in the
named range, and then click in the "name box", and type in the name you
wish, and then <Enter>.

Enter a number in the first cell and another in the last cell and make sure
that
=SUM(ccell)
returns the correct total.
 
Back
Top