sum of more than 30 non-adjacent cells

[Picman]

I have a column with hundreds of populated cells, and I would like to add up
the sum of more than 30 of these cells. The problem is that they are not
adjacent to each other and the Excel SUM function seems to limit the number
of arguments to 30. Is there a way around this?

 

[Niek Otten]

=SUM(A1,B1)+SUM(D1:F1)....etc

 

[John C]

Are the cells at regularly spaced intervals?
If not, do the cells have some unique identifier in the same row that they
are in?

If not, remember, there are 30 arguments in each sum, you could nest your
sum statements:
=SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612))

 

[Picman]

Yes they are at regularly spaced intervals.

Are the cells at regularly spaced intervals?
If not, do the cells have some unique identifier in the same row that they
are in?

If not, remember, there are 30 arguments in each sum, you could nest your
sum statements:
=SUM(SUM(A1,B4,C5,...),SUM(J13,K27,M612))

 

[John C]

Regularly spaced intervals is easy enough to do:
Suppose your range goes through row 100
start = your starting row within the range, so say the first number you want
summed is in row 11, you would substitute 11 for the 2 appearances of start
in the formula, likewise, you would substitute for nth however many rows is
the regular interval.
=SUMPRODUCT(--(MOD(ROW(A1:A100)-start,nth)=0),--(ROW(A1:A100)>=start),A1:A100)

If this doesn't seem to work for you, post back with column reference,
starting point, how many rows, etc.

 

[Rick Rothstein]

It is always helpful to tell us what you have (in this case, the starting
row for you data and the regular spaced interval) so we can give you an
exact formula for it. Since you didn't tell us, here is an example which you
can modify for your situation. Let's say your data is in Column C and starts
on Row 4; let's also say the rows you want to sum up are spaced 3 rows apart
(that is, the rows you want to add up are C4, C7, C10, C13, etc.); then
assuming your data does not extend beyond Row 500, this formula will sum up
those rows in Column C...

=SUMPRODUCT((MOD(ROW(C4:C500)-ROW(C4),3)=0)*C4:C500)

A general format for this formula would be...

=SUMPRODUCT((MOD(ROW(<StartRow>:<EndRow>)-Row(<StartRow>),<Interval>)=0)*<StartRow>:<EndRow>)

 

[Rick Rothstein]

Actually, we can eliminate one of the function calls...

=SUMPRODUCT((MOD(ROW(C4:C500)-4,3)=0)*C4:C500)

and here is the modified generalized formula (I've also changed the
descriptions a little bit to make them more accurate)...

=SUMPRODUCT((MOD(ROW(<StartCell>:<EndCell>)-<StartingRowNumber>,<Interval>)=0)*<StartCell>:<EndCell>)

where StartCell and EndCell are in the same column.

 

[Picman]

I seem to be missing something because I get an error.
My perameters are as follows:
Range = "e3:e301", the first cell to be added is "e3" and the second is
"e7", then "e11", then "e15" etc. Basically every fourth row.

 

[John C]

=SUMPRODUCT(--(MOD(ROW(A1:A100)-start,nth)=0),--(ROW(A1:A100)>=start),A1:A100)

changes to

=SUMPRODUCT(--(MOD(ROW(E1:E1000)-3,4)=0),--(ROW(E1:E1000)>=3),E1:E1000)

is my formula modified

 

[Rick Rothstein]

Substituting your conditions into this general format...

=SUMPRODUCT((MOD(ROW(<StartCell>:<EndCell>)-<StartingRowNumber>,<Interval>)=0)*<StartCell>:<EndCell>)

yeilds this formula...

=SUMPRODUCT((MOD(ROW(E3:E301)-3,4)=0)*E3:E301)

 

[T. Valko]

Try this:

=SUMPRODUCT(--(MOD(ROW(E3:E301)-ROW(E3),4)=0),E3:E301)

 

[Picman]

That worked like a charm. Thanks very much guys, i appreciate all of the help.

 

[T. Valko]

On behalf of all of us, you're welcome!