Summing +30 Non-adjacent cells
 

Good afternoon,

I need to sum over 30 cells which are nonadjacent. However, they are evenly
spaced (the first cell is D4, and then H4, and soforth, through FH4).

Currently, I am simply using the SUM function and various "embedded" SUMs,
but this is very long and tedious to input (not to mention, looks horribly
sloppy in the Fx field). Is there a simpler way to tally this information?

Thank you again for your kind support.

Summing +30 Non-adjacent cells
 

Try this:

=SUMPRODUCT(--(MOD(COLUMN(D4:FH4)-COLUMN(D4),4)=0),D4:FH4)

--
Biff
Microsoft Excel MVP


"Stacy C" wrote in message
...
Good afternoon,

I need to sum over 30 cells which are nonadjacent. However, they are
evenly
spaced (the first cell is D4, and then H4, and soforth, through FH4).

Currently, I am simply using the SUM function and various "embedded" SUMs,
but this is very long and tedious to input (not to mention, looks horribly
sloppy in the Fx field). Is there a simpler way to tally this information?

Thank you again for your kind support.

Summing +30 Non-adjacent cells
 

Hi,

Try this

=SUMPRODUCT(D4:FH4*(MOD(COLUMN(D:FH),4)=0))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Stacy C" wrote:

Good afternoon,

I need to sum over 30 cells which are nonadjacent. However, they are evenly
spaced (the first cell is D4, and then H4, and soforth, through FH4).

Currently, I am simply using the SUM function and various "embedded" SUMs,
but this is very long and tedious to input (not to mention, looks horribly
sloppy in the Fx field). Is there a simpler way to tally this information?

Thank you again for your kind support.

Summing +30 Non-adjacent cells
 

=SUMPRODUCT(D4:FH4*(MOD(COLUMN(D:FH),4)=0))

That'll return an error if any of the in-between cells contain text.


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Try this

=SUMPRODUCT(D4:FH4*(MOD(COLUMN(D:FH),4)=0))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Stacy C" wrote:

Good afternoon,

I need to sum over 30 cells which are nonadjacent. However, they are
evenly
spaced (the first cell is D4, and then H4, and soforth, through FH4).

Currently, I am simply using the SUM function and various "embedded"
SUMs,
but this is very long and tedious to input (not to mention, looks
horribly
sloppy in the Fx field). Is there a simpler way to tally this
information?

Thank you again for your kind support.

Summing +30 Non-adjacent cells
 

"T. Valko" wrote...
Try this:

=SUMPRODUCT(--(MOD(COLUMN(D4:FH4)-COLUMN(D4),4)=0),D4:FH4)
....

This is the best approach when there's a pattern, but the really
general approach would be to use multiple area ranges, e.g.,

=SUM((D2,D3,D5,D7,D11,D13,D17,D19,D23,D29,D31,D37,
D41,D43,D47,D53,D59,D61,D67,D71,D73,D79,D83,D89,D9 7,
D101,D103,D107,D109,D113,D127,D131,D137,D139,D149) )

Note the inner set of parentheses. That's one argument to SUM. That
one argument is a 35 area range.

Summing +30 Non-adjacent cells
 

I had entered the individual columns, but this spreadsheet will always have
the fields evenly spaced. I just wanted something a bit cleaner than a bunch
of parentheses :)

Thank you kindly.

"Harlan Grove" wrote:

"T. Valko" wrote...
Try this:

=SUMPRODUCT(--(MOD(COLUMN(D4:FH4)-COLUMN(D4),4)=0),D4:FH4)
....

This is the best approach when there's a pattern, but the really
general approach would be to use multiple area ranges, e.g.,

=SUM((D2,D3,D5,D7,D11,D13,D17,D19,D23,D29,D31,D37,
D41,D43,D47,D53,D59,D61,D67,D71,D73,D79,D83,D89,D9 7,
D101,D103,D107,D109,D113,D127,D131,D137,D139,D149) )

Note the inner set of parentheses. That's one argument to SUM. That
one argument is a 35 area range.