Sum more than 30 cells

[Lynn Bartling]

I am adding every third line down a spreadsheet and I think I've done this
before...I have more than 30 cells I'm adding together. How do I do it.
I'm sure it is easy but I can't remember how I did it.
Thanks so much for any help.
Lynn

 

[Aladin Akyurek]

=SUMPRODUCT(--(MOD(ROW($A$1:$A$200)-CELL("Row",$A$1)+0,3)=0),$A$1:$A$200)

 

[Ken Wright]

Assuming you want every third row of the range Ax:A1000, with x being the row
your data starts on:-


If your data starts on Row 1, 4, 7, 10, 13 etc

=SUMPRODUCT((MOD(ROW($A$4:$A$1000),3)=0)*($A$4:$A$1000))

If your data starts on Row 2, 5, 8, 11, 14 etc

=SUMPRODUCT((MOD(ROW($A$5:$A$1000),3)=1)*($A$5:$A$1000))

If your data starts on Row 3, 6, 9, 12, 15 etc

=SUMPRODUCT((MOD(ROW($A$6:$A$1000),3)=2)*($A$6:$A$1000))

 

[Ken Wright]

=SUMPRODUCT((MOD(ROW($A$8:$A$1004),3)=MOD(ROW($A$8)-1,3))*($A$8:$A$1004))

Saves you having to worry about where it starts

 

[Lynn Bartling]

Thank you all for your help.
Lynn

 

[Lynn Bartling]

When I said "sum" is this going to add all the cells? That is what I need.
Lynn

 

[Lynn Bartling]

I need to add B5, B7, B11 all the way down to at least B137 and I will need
to add B6, B8, B12 all the way down to B139. Then I'm doing that same
calculation all across the bottom of the spreadsheet for several rows.
Thanks for your help.
Lynn

 

[Lynn Bartling]

I mean I need to add B5, B8, B11..

I need to add B5, B7, B11 all the way down to at least B137 and I will need
to add B6, B8, B12 all the way down to B139. Then I'm doing that same
calculation all across the bottom of the spreadsheet for several rows.
Thanks for your help.
Lynn

 

[Aladin Akyurek]

I mean I need to add B5, B8, B11..

Is it not:

=SUMPRODUCT(--(MOD(ROW($B$5:$B$137)-CELL("Row",$B$5)+0,3)=0),$B$5:$B$137)

 

[Lynn Bartling]

I did not get the right total with this formula (if I clicked and added
every cell I did have the right total). This looks like a little more than
I wanted as far as the formula...isn't there a way I can add more than 30
cells without doing it this way? I don't mind clicking on every cell...I'd
rather do that then this formula...but is that possible? I really
appreciate your help. I have a deadline and feel the stress of not being
able to get my totals at the bottom of my spreadsheet - any additional help
is greatly appreciated.
Lynn

 

[Peo Sjoblom]

I would use the formula provided and maybe edit it for your particular set
of data;
However you can use more that 30 in a sum formula, just use extra
parenthesis, i.e.

=SUM((B5,B7 and so on 30 times, then),add another set of 30 and so on, for
each set add an extra parenthesis..
Could look like

=SUM(((B2,B4,B6,B8,B10,B12,B14,B16,B18,B20,B24,B26,B28,B30,B32,B35,B37,B39,B
41,B43,B45,B47,B49,B51,B53,B55,B57,B59,B610,B61,B63,B65,B68,B70,B72,B74,B77,
B79,B81,B82,B85,B88,B90,B93,B95,B97,B99,B101,B103,B104,B106,B108,B110,B112,B
114,B116),B121,B124,B127))

 

[Norman Harker]

Hi Lynn!

Two formula approaches depending upon what you want:

Sums the 3rd cell and every 3rd cell thereafter (A7+A10+A13...)
=SUMPRODUCT((MOD(ROW($A$5:$A$100),3)=MOD(ROW($A$5)-1,3))*($A$5:$A$100)
)

Sums the 1st cell and every 3rd cell thereafter (A5+A8+A11...)
=SUMPRODUCT((MOD(ROW(A5:A100)-CELL("Row",A5:A100)+0,3)=0)*(A5:A100))

But if you want to add non evenly spaced cells then:

=SUM(A7,A9,A10,A15,A18....)

If you need to exceed the 30 number limit of SUM

=SUM(A7,A9,A10,A15,A18....)+SUM(A56,A57,A59,A65....)

One of your posts indicated that this last approach was what you want:

"I need to add B5, B7, B11 all the way down to at least B137 and I
will need
to add B6, B8, B12 all the way down to B139."

But there may be some criteria that you are using to determine what
cells to sum and if you provide that criteria, we might be able to
improve on this.

Another way might be to insert a helper column to put a flag against
those that need to be summed. That flag can then be used as the sum
selection criteria.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Lynn Bartling]

THANK YOU SO MUCH! The one I wanted (and I know I've used it before) is
where I add a bunch together and then +SUM and add a bunch more. I know it
is more time consuming, but sure makes it easy to drag across the page and
get my totals for everything. THANK YOU! I'm really not that swift with
excel and sending a template to other not so swift excel users, so the
easier the better. Thanks again!
Lynn

 

[Peo Sjoblom]

You don't even have to + sum them at all if you follow my instructions, just
use parenthesis and commas and you'll be alright.