How can I sum every third cell in a row in Excel

[Guest]

I have a fairly large spreadsheet. In a particularl section I would like to
sum the values contained in every third cell. Currently I have over 30 cells
I would like to sum and the only way I can find to do so is by referencing
each cell individually with a + sign in front. All of the +cell references is
cumbersome and inefficient.

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(MOD(ROW(D130),3)=0),D130)

will sum D3,D6, D9 and so on

 

[Guest]

Thank you for the reply. I cut and pasted you suggestion into my sheet and
modified the array range to fit my requirements (S14:GV14). however, the
result was a value# error. I will see if I can trace the error.

ken

 

[Peo Sjoblom]

No need to trace, I assumed rows you are going across, use

=SUMPRODUCT(--(MOD(COLUMN(S14:GV14),3)=0),S14:GV14)


--

Regards,

Peo Sjoblom

 

[Bruno Campanini]

Thank you for the reply. I cut and pasted you suggestion into my sheet and
modified the array range to fit my requirements (S14:GV14). however, the
result was a value# error. I will see if I can trace the error.

ken

If you have a Row range such as A10:A45:
=SUMPRODUCT(--(NOT(MOD(ROW(Range),3))),(Range))

If you have a Column range such as A10:K10:
=SUMPRODUCT(--(NOT(MOD(COLUMN(Range),3))),(Range))

Bruno