Why a formula like =SUMPRODUCT(A1:A11,MOD(ROW(A1:A11),2)) which will add the
cells A1, A3, A5, A7, A9 and A11?

If you want to do it in VBA, use a loop with a step equals to 2:
Total = 0
For iRow = 1 To 11 Step 2
Total = Total + Cells(iRow, 1)
Next iRow