SUMPRODUCT statement

[Matt Brown via OfficeKB.com]

This is my problem: -

=SUMPRODUCT((A1:A2=B2)*(F1:F2="JAN04")*(G1:G2))

=SUMPRODUCT((J1:J2=K2)*(F1:F2="JAN04")*(G1:G2))

How do i combine or nest these statements so that if either or conditions (A1:A2=B2) / (J1:J2=K2) are true it will sum G1:G2 where F1:F2 = jan04.

This is numbing my brain so any help would be greatly aspreciated.

Matt

*****************************************
* This message was posted via http://www.officekb.com
*
* Report spam or abuse by clicking the following URL:
* http://www.officekb.com/Uwe/Abuse.aspx?aid=3318578887d44d3f9424dce76b1fd515
*****************************************

 

[Dave Peterson]

One way:

=SUMPRODUCT((((J1:J2=K2)+(A1:A2=B2))>0)*(F1:F2="JAN04")*(G1:G2))

The asterisk represents multiply/And.
the plus represents Add/Or.

 

[Dave Peterson]

In fact,

=SUMPRODUCT(((J1:J2=K2)+(A1:A2=B2))*(F1:F2="JAN04")*(G1:G2))

will work, too.

I like the first--just because I think it's more self-documenting.

 

[Myrna Larson]

If there's no possibility that both B2 and K2 are a match, you can just add
them:

=SUMPRODUCT((A1:A2=B2)*(F1:F2="JAN04")*(G1:G2))+
SUMPRODUCT((J1:J2=K2)*(F1:F2="JAN04")*(G1:G2))

But if both match, you'll add G1:G2 twice.

In that case

=SUMPRODUCT( --(((A1:A2=B2)+(J1:J2=K2))>0),--(F1:F2="JAN04"),G1:G2)

This is my problem: -

=SUMPRODUCT((A1:A2=B2)*(F1:F2="JAN04")*(G1:G2))

=SUMPRODUCT((J1:J2=K2)*(F1:F2="JAN04")*(G1:G2))

How do i combine or nest these statements so that if either or conditions

(A1:A2=B2) / (J1:J2=K2) are true it will sum G1:G2 where F1:F2 = jan04.

 

[Myrna Larson]

Hi, Dave:

I'm not sure that's correct: Let's say everything matches on row 2, i.e.
J2=K2, A2=B2, and F2="JAN04". The formula gives

(1+1)*1*G2
= 2*1*G2
= 2*G2

I think you need the >0 that you had in your first post.

OTOH, if there's no possiblity of a match for both J and A, it's OK.

 

[Matt Brown via OfficeKB.com]

Hi,

Thanks so much it works a treat.

Regards

Matt

*****************************************
* A copy of the whole thread can be found at:
* http://www.officekb.com/uwe/Forum.aspx/excel-functions/26005
*
* Report spam or abuse by clicking the following URL:
* http://www.officekb.com/Uwe/Abuse.aspx?aid=d7ebe6129a4545a587ba25a866d7dbb3
*****************************************

 

[Dave Peterson]

Oops. Bad testing on my part!

Thanks for the correction.