Help simplifying a SUMPRODUCT formula

  • Thread starter Thread starter David Lipetz
  • Start date Start date
D

David Lipetz

The following SUMPRODUCT formula produces the correct results but I'm
reasonably certain that there must be a more efficient way of constructing
the formula. There are basically two components to this formula separated by
the + sign. Each component performs the same calculation: the first part for
Class="MS" and the second part (after the +) for Class="MSTV". Isn't there a
way to construct the formula so that it would be calculated for Class="MS"
OR Class="MSTV" and eliminate the need for two steps? I tried to incorporate
the OR formula within the SUMPRODUCT formula but was unsuccessful.

=(SUMPRODUCT(--(Void="Normal"),--(MONTH(Date)=MONTH(B69)),--(Type="Invoice"),--(Class="MS"),(Sell)))-(SUMPRODUCT(--(Void="Normal"),--(MONTH(Date)=MONTH(B69)),--(Type="Return"),--(Class="MS"),(Sell)))+(SUMPRODUCT(--(Void="Normal"),--(MONTH(Date)=MONTH(B69)),--(Type="Invoice"),--(Class="MSTV"),(Sell)))-(SUMPRODUCT(--(Void="Normal"),--(MONTH(Date)=MONTH(B69)),--(Type="Return"),--(Class="MSTV"),(Sell)))

Ideas?

Thanks,
David
 
Outstanding! Thank you.

If you have a moment, care to explain the logic with the formula? I've
looked at the Help files for MATCH and am not quite sure why this works.
Once I understand the logic, I won't have to ask again.

Thanks again,
David
 
--(ISNUMBER(MATCH(Class,{"MS","MSTV"},0)))

Let's assume Class = A1:A5

A1 = MS
A2 = XX
A3 = MSTV
A4 = MS
A5 = XX

MATCH returns the relative position of the lookup_value within the
lookup_array if it matches a value in the lookup_array.

Class contains the lookup_values and {"MS","MSTV"} is the lookup_array.

A1 (MS) matches MS in the lookup_array and MS is in position 1 of the
lookup_array so:

MATCH(A1,{"MS","MSTV"},0) = 1

A2 (XX) does not match any value in the lookup_array so:

MATCH(A2,{"MS","MSTV"},0) = #N/A

A3 (MSTV) matches MSTV in the lookup_array and MSTV is in position 2 of the
lookup_array so:

MATCH(A3,{"MS","MSTV"},0) = 2

MATCH(Class,{"MS","MSTV"},0) passes the array of these values to ISNUMBER:

ISNUMBER({1;#N/A;2;1;#N/A})

ISNUMBER evaluates each element of that array to see if it is a number. If
it is, it evaluates to TRUE and if it doesn't it evalutes to FALSE:

({TRUE;FALSE;TRUE;TRUE;FALSE})

The double unary "--" then coerces these logicals to either 1 or 0 which
SUMPRODUCT needs to work:

--({TRUE;FALSE;TRUE;TRUE;FALSE})

{1;0;1;1;0}
 
Once again, I thank you. A well written explanation that makes perfect
sense. This will be very helpful!

Regards,
David
 
Back
Top