Summing +30 Non-adjacent cells

[Stacy C]

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.

 

[T. Valko]

Try this:

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

 

[Shane Devenshire]

Hi,

Try this

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

 

[T. Valko]

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

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

 

[Harlan Grove]

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,D97,
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.

 

[Stacy C]

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.