Looking for an Excel function

  • Thread starter Thread starter Jean
  • Start date Start date
J

Jean

Is there a function or formula that I could use to add
every OTHER cell in a range? In other words, I would
rather not type =sum(a1,a3,a5,a7,a9...). Isn't there
some way that I could indicate this in a formula.
 
Thank you so, so much!!!

-----Original Message-----
Jean

=SUMPRODUCT((MOD(ROW(A1:A800),2)=1)*A1:A800)

Will add every second cell in column A starting at A1.

=SUMPRODUCT((MOD(COLUMN(A1:BH1),2)=1)*A1:BH1)

Will add every second cell in row 1 across 60 columns starting in A1.

Gord Dibben Excel MVP - XL97 SR2 & XL2002
Click to expand...
 
One that would survive inserting a row before the data involving a vertical
range...

=SUMPRODUCT((MOD(ROW($A$1:$A$6)-CELL("Row",$A$1:$A$6)+H1,H2)=0)*($A$1:$A$6))

H1 set to 0 and H2 to 2 would some every second value in A1:A6 starting with
the first value in A1. When H1 is set 1 and H2 to 2, it will sum every 2nd
value starting the first 2nd value.

For a horizontal range...

=SUMPRODUCT((MOD(COLUMN($B$2:$F$2)-CELL("Col",$B$2:$F$2)+H1,H2)=0)*($B$2:$F$
2))
 
Back
Top