Help needed with sum function

[Angela]

Hello,

Hopefully i can have a few questions answered all at once here.

1) Is the sum function within excel limited to approximately 30 cells
?

e.g. when I sum the cells =sum(c14,c22,c30,c38,c46……… it
seems to stop after I get to about 30 cells.

2) Is there a difference in summing them like that or like this
=sum(c14+c22+c30+……….

3) is there a quicker way to sum cells when they are each a constant
number apart, like the above scenario where each cell number I want to
sum increases by 8 each time ?

I have about 52 cells to sum (one for each week of the year) and I am
thinking the only other way I can do it is to sum 10 at a time, then
do a final sum on those 6 x values but I would like it to be a bit
neater.

I thought there might have been an option somewhere like

=sum($C14+($n+8)+$C398) where the starting cell is c14 and the end
cell is c398 and it sums every multiple cell of 8 in between.

(I just made that up of course, but it's sort of the formula i am
trying to achieve).


Regards and many thanks in advance,

Angela

 

[Jerry W. Lewis]

Functions (such as SUM) that take a variable number of arguments are
limited to 30 arguments, not 30 cells.
=SUM(C14+C22+C30,C38+C46+C54)
uses 2 arguments instead of 6, but this approach is a pain to enter and
would rapidly become unacceptably long.
=C14+C22+C30+C38+C46+C54
is slightly shorter, but remains inconvenient to enter.

If by "quicker" you mean easier to enter, then you might try
=SUM(IF(MOD(ROW(C14:C422),8)=6,C14:C422))
which must be array entered (Ctrl-Shift-Enter)

Jerry

 

[Angela]

Wow Jerry, i will try this when i go back to work tomorrow - my
fingers are crossed, and i am expecting it to work because you sound
like you know what you are talking about

Thanks ever so much,
Angela

 

[Jon Quixley]

Angela,
Yes to the first two questions - there is a limit of 30 which ever o
these ways you try.

Is it possible to set up another line or column to get thse 52 answer
into 52 adjacent cells ? Assuming that the answers are in C14, C22, C3
etc, yo9u can set up another line so that column D reads column C s
cell D1 is =C14, D2 is =C22 etc. Once you have this then you can se
the sum function =Sum(D153) at the end of the column

There is a way of getting the formula to step every eighth cell, but t
my mind this is making things more complicated than they need to be. I
the above suggestion doesn't work , then we'll go to option B

Cheer

 

[RagDyeR]

Another option is to take Jerry's formula and change functions to
SumProduct, therefore eliminating the need for an array formula.

With just a normal <Enter>, try this:

=SUMPRODUCT((MOD(ROW(C14:C422),8)=6)*C14:C422)

--

HTH,

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

Wow Jerry, i will try this when i go back to work tomorrow - my
fingers are crossed, and i am expecting it to work because you sound
like you know what you are talking about

Thanks ever so much,
Angela

 

[Bob Umlas]

You can also "fool" excel into going beyond the 30-cell limit by using extra
parentheses:
=SUM(C14,C22,C30,C38,C46) has Excel think there are 5 items, but
=SUM(C14,C22,(C30,C38,C46)) has Excel think there are only 2 items!!
So, you can get hundreds of cells (not evenly spaced or together, for
example) by something like:
=SUM(C10,C123,F9,(E17,R12,C11,C15),(group3 like
previous),(group4...),(group5)) and excel will think there are 7 with this
configuration or,
=SUM((C10,C123,F9,(E17,R12,C11,C15)),((group3 like
previous),(group4...),(group5))) and excel will think there are only 2!!
Bob Umlas
Excel MVP

 

[RagDyer]

This also works with *one* double pair of parenthesis:

=SUM((cell1,cell2, ... cell100))

 

[Harlan Grove]

Bob Umlas wrote...

You can also "fool" excel into going beyond the 30-cell limit by using extra
parentheses:

....

It's a 30 *argument* limit. The 'extra' parentheses create multiple
area range references that Excel considers single arguments.

=SUM(C14,C22,(C30,C38,C46)) has Excel think there are only 2 items!!

No, there are 3 arguments: 2 single cell references, C14 and C22, and a
multiple area range reference, (C30,C38,C46).

So, you can get hundreds of cells (not evenly spaced or together, for

As a practical matter, the formula limit of 1024 characters puts a
ceiling of 312 cells that could be references in one formula. For cells
all below row 9 the upper limit would be 254 cells.

That said, monster formulas consisting mostly of individual cell
references provide more potential for formula errors than almost
anything else.