• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sumproduct function on non-continuous ranges

O

ornshrn

New Member
Hi,

Is it possible to apply the sumproduct function on non-continuous ranges? For example, I want to multiply (A1: A5, A8:A12) with (B1:B5, B8:B12).

Thanks
Oren
 
Hi Oren ,

Is it not the same as adding 2 separate SUMPRODUCT functions as in :

=SUMPRODUCT((A1:A5)*(B1:B5)) + SUMPRODUCT((A8:A12)*(B8:B12))

Narayan
 
Hi Narayan,

It is, but let's say I have 10, 20 or 50 non continuous cells? I think this is the challenge.

Oren
 
Hi Oren ,

Suppose you wanted a sumproduct where there were really non-contiguous ranges involved , as in :

A1 * B7 + A7 * B9 + A13 * B12 + A17 * B15

you could use the following :

=SUMPRODUCT(N(OFFSET(A1,{0;6;12;16},))*N(OFFSET(B1,{6;8;11;14},)))

Narayan
 
You can often use a Helper Row/Column to assist in these odd/non-consistant scenarios

Can you post an example ?
 
I19: =SUMPRODUCT(($B$3:$B$16<>"A")*$C$3:$C$16,F$3:F$16)*0.78
Copy across but then adjust the trailing multiplier

J19: =SUMPRODUCT(($B$3:$B$16<>"A")*$C$3:$C$16,G$3:G$16)*0.17
K19: =SUMPRODUCT(($B$3:$B$16<>"A")*$C$3:$C$16,H$3:H$16)*0.05
 
You must log in or register to reply here.
Back
Top