SUMPRODUCT

[Mike]

Hi.

In SUMPRODUCT, is there a way to specify the arrays to
cover non-sequential cells? For instance, I want to use
sumproduct by specifying the following arrays:

Array1 --> A1,A7,A13,A20
Array2 --> B1,B7,B13,B20

Thanks,
Mike
..

 

[Harlan Grove]

In SUMPRODUCT, is there a way to specify the arrays to
cover non-sequential cells? For instance, I want to use
sumproduct by specifying the following arrays:

Array1 --> A1,A7,A13,A20
Array2 --> B1,B7,B13,B20

Only using OFFSET like so,

=SUMPRODUCT(N(OFFSET(A1,{0;6;12;19},0,1,1)),
N(OFFSET(B1,{0;6;12;19},0,1,1)))

 

[Peo Sjoblom]

Only using OFFSET like so,

=SUMPRODUCT(N(OFFSET(A1,{0;6;12;19},0,1,1)),
N(OFFSET(B1,{0;6;12;19},0,1,1)))

Or if the OP made a typo and wanted every 6th row and instead of A20 meant
A19, A25 and so on

=SUMPRODUCT(--(MOD(ROW(A1:A50),6)=1),--(A1:A50),--(MOD(ROW(B1:B50),6)=1),--(
B1:B50))

 

[Harlan Grove]

Or if the OP made a typo and wanted every 6th row and instead of A20 meant
A19, A25 and so on

=SUMPRODUCT(--(MOD(ROW(A1:A50),6)=1),--(A1:A50),
--(MOD(ROW(B1:B50),6)=1),--(B1:B50))

Now a nicely fractured thread. OP multiposted in .worksheet.functions, and
J.E. responded there with something similar.

Note that --(MOD(ROW(B1:B50),6)=1) is entirely redundant since the only
thing that would have it return anything different
than --(MOD(ROW(A1:A50),6)=1) would be inserting or deleting cells in column
A but not column B or vise versa.