We help IT Professionals succeed at work.

How to shorten a sumproduct formula

Petersburg1
Petersburg1 asked
on
Dear Experts,
below you can see my formula. The formula is working but extremely long because I did not now how to implement OR and so I did it with plus. In the column $F:$F I have A11 and A12 values on which the formula shall act.
thanks
Nils
 

=((SUMPRODUCT(('Data-Import'!$E$2:$E$9997=$A$2)*('Data-Import'!$F$2:$F$9997=$A11)*('Data-Import'!$L$2:$L$9997))+SUMPRODUCT(('Data-Import'!$E$2:$E$9997=$A$2)*('Data-Import'!$F$2:$F$9997=$A12)*('Data-Import'!$L$2:$L$9997)))-(SUMPRODUCT(('Data-Import'!$E$2:$E$9997=$A$2)*('Data-Import'!$F$2:$F$9997=$A11)*('Data-Import'!$M$2:$M$9997))+SUMPRODUCT(('Data-Import'!$E$2:$E$9997=$A$2)*('Data-Import'!$F$2:$F$9997=$A12)*('Data-Import'!$M$2:$M$9997))))/(N11+N12)
Comment
Watch Question

See if this works

=((SUMPRODUCT(('Data-import'!$E$2:$E$9997=$A$2)*(('Data-import'!$F$2:$F$9997=$A11)+('Data-import'!$F$2:$F$9997=$A12))*('Data-import'!$L$2:$L$9997)))-(SUMPRODUCT(('Data-import'!$E$2:$E$9997=$A$2)*(('Data-import'!$F$2:$F$9997=$A11)+('Data-import'!$F$2:$F$9997=$A12))*('Data-import'!$M$2:$M$9997))))/(N11+N12)
Carlos RamirezFreelance Web Developer

Commented:
No points but just an idea -

I don't use SUMPRODUCT much, but I would see about using named ranges.  Instead of 'Data-Import'!E$2:$E9997, name the range "DI_E"and column F, "DI_F" and so on.  This alone may cut it down about 25 chars with each replacement.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Here's what I see: SUMPR ( (E=A2)*(F=A11)*(L)+(E=A2)*(F=A12)*(L) ) - ( (E=A2)*(F=A12)*(M)+(E=A2)*(F=A12)*(M) )

You should be able to factor out (E=A2) from the entire equation, and L from the left side, M on the right, leaving:

SUMPR ( (E=A2) * ( (L) * ( (F=A11)+(F=A12) ) - ( (M) * ( (F=A11)+(F=A12) ) ) ) )

=SUMPRODUCT(('Data-import'!$E$2:$E$9997=$A$2)*(('Data-import'!$L$2:$L$9997)*(('Data-import'!$F$2:$F$9997=$A11)+('Data-import'!$F$2:$F$9997=$A12))-(('Data-import'!$M$2:$M$9997)*(('Data-import'!$F$2:$F$9997=$A11)+('Data-import'!$F$2:$F$9997=$A12)))))/(N11+N12)

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
This may also be true:
SUMPR ( (E=A2) * ( (L) * ( (F=A11)+(F=A12) ) - ( (M) * ( (F=A11)+(F=A12) ) ) ) )

same as:

SUMPR ( (E=A2) * ( (F=A11) + (F=A12) ) * ( L - M ) )

which becomes:

=SUMPRODUCT(('Data-import'!$E$2:$E$9997=$A$2)*(('Data-import'!$F$2:$F$9997=$A11)+('Data-import'!$F$2:$F$9997=$A12))*(('Data-import'!$L$2:$L$9997)-('Data-import'!$M$2:$M$9997)))/(N11+N12)

With test data, I get the same answer with the OP, my last post, and now this one.

Cheers,

Dave

Author

Commented:
Hi Dave,
if I use your formula I get #Name?....

here what I used...
=SUMPODUCT( ('Data-Import'!$E:$E=A2)*( ('Data-Import'!$L:$L)*( ('Data-Import'!$F:$F=A11)+('Data-Import'!$F:$F=A12) )-( ('Data-Import'!$M:$M)*( ('Data-Import'!$F:$F=A11)+('Data-Import'!$F:$F=A12) ) ) ) )

thanks
Nils
Most Valuable Expert 2012
Top Expert 2012

Commented:
Try SUMPRODUCT, rather than SUMPODUCT?
Most Valuable Expert 2012
Top Expert 2012
Commented:
Also, I recommend my last post.  With your simplification (though you may pay for the A:A instead of A2:A9997...):

=SUMPRODUCT(('Data-import'!$E:$E=$A$2)*(('Data-import'!$F:$F=$A11)+('Data-import'!$F:$F=$A12))*(('Data-import'!$L:$L)-('Data-import'!$M:$M)))/(N11+N12)

See attached, with all options, including this last post.

Dave
sumprod-simplification-r1.xlsx
Most Valuable Expert 2012
Top Expert 2012

Commented:
PS (sorry for the repeat post, here, not meaning to spam you guys).  If you pull up the demo worksheet and repeatedly hit F9 to recalc the random numbers, you'll see that its MUCH MUCH faster, if you include the row ranges, rather than entire column ranges, as you did in your last post.

Cheers,

Dave

Author

Commented:
Super!
thank you very much!!!
Nils

Author

Commented:
very nice!
thanks a lot
Nils