SUMPRODUCT using odd and even rows

[Stan Dad]

I have a column of numbers, say 1,2,3,4,5,6 in cells A1,A2,A3,A4,A5,A6. I want the sum of the alternating products, i.e. A1*A2+A3*A4+A5*A6. I have tried SUMPRODUCT(--(MOD(ROW(A1:A4),2)=1),A1:A4,--(MOD(ROW(A1:A4),2)=0),A1:A4) but this returns a zero value.

Any ideas?

 

[steve the fish]

This maybe:

=SUMPRODUCT(A1:A6*ISODD(ROW(A1:A6)),OFFSET(A1:A6,1,0))

 

[Stan Dad]

Many thanks Steve, this works ... kinda.

The slight hesitation is because the formula works if the range starts on an odd-numbered row (A1, A3, etc), but not if it's an even numbered one. Until the relevant bit of the formula is amended to ISEVEN(ROW(A2:A7).

I posted a cut down version of my spreadsheet for simplicity, and I have several rows of other data above this; which means that every time I add a row above the formula, it will need to be amended accordingly. Is there a solution that isn't sensitive in this way?

This is not to take anything away form Steve's solution - I'm further forward than I was 12 hours ago: many thanks Steve.

 

[Aladin Akyurek]

Does this remedy the situation?

=SUMPRODUCT(A1:A6*(MOD(ROW(A1:A6)-ROW(A1),2)=0),OFFSET(A1:A6,1,0))

 

[Stan Dad]

It does indeed - much appreciated.

 

[Aladin Akyurek]

It does indeed - much appreciated.

You are welcome.