SUMPRODUCT

  • Thread starter Thread starter Mike
  • Start date Start date
M

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
..
 
Mike said:
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
Click to expand...

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)))
 
Harlan Grove said:
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)))
Click to expand...


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))
 
Peo Sjoblom said:
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))
Click to expand...

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.
 
Back
Top