use of sum by skiping one or more cells

F

Faisal Yameen

I want to sum only even cells from A1: A1000
I used =a2+a4+a6+a8+…………..+a1000
Used this formula n cell b1
But now I want to solve this problem by some short formula
Help me thank you
 
B

Bob Phillips

Hi Faisal,

Try this

=SUM((A1:A1000)*(MOD(ROW(INDIRECT("1:1000")),2)=0))

it is an array formula, so commit with Ctrl-Shift-Delete
 
A

Aladin Akyurek

=SUMPRODUCT(--(MOD(ROW(A2:A1000)-CELL("Row",A2)+0,2)=0),A2:A1000)

+0 means starting with A2. When changed to +1, it will sum from the
first 2nd on.
 
Top