MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Please Help me in this Formula

  Asked By: Almas    Date: Nov 07    Category: MS Office    Views: 648

I am using two Sum products formula with multiple Conditions.

=SUMPRODUCT(('30+ MOB'!G2:G65536=SUMMERY!B2)*('30+
MOB'!C2:C65536=SUMMERY!B3)*('30+ MOB'!B2:B65536=SUMMERY!B4)*('30+
MOB'!M2:M65536))/SUMPRODUCT(('30+ MOB'!G2:G65536=SUMMERY!B2)*('30+
MOB'!C2:C65536=SUMMERY!B3)*('30+ MOB'!B2:B65536=SUMMERY!B4)*('30+

I want to make it in One Please Suggest.



2 Answers Found

Answer #1    Answered By: Sairish Kauser     Answered On: Nov 07

Not sure if this simplified view makes sense:

Looks like your Arrays 1,2 & 3 have the common 'selection' logic across the two
functions. The difference applies to (Array5)/(Array4).

Which translates to:

Answer #2    Answered By: Javairea Akram     Answered On: Nov 07

It is possible to do more complicated calculations in a SumProduct than just
a simple sum. For instance, the following is perfectly legal syntax:

=SUMPRODUCT(('30+ MOB'!G2:G99=SUMMERY!B2)*('30+
MOB'!C2:C99=SUMMERY!B3)*('30+ MOB'!B2:B99=SUMMERY!B4)*('30+ MOB'!M2:M99/'30+

This does the calculation M/K for each row in the range and sums them if the
other criteria are met. However, there are two things to note about this.

1) It does the division whether you want the row or not. My limit at row
99 instead of 65536 was to stop it doing the divisions on rows that didn't
have values in column K. For such rows, you'll get a #DIV/0 result, whether
or not the row is otherwise selected. This error can be avoided. E.g.:

=SUMPRODUCT(('30+ MOB'!G2:G99=SUMMERY!B2)*('30+
MOB'!C2:C99=SUMMERY!B3)*('30+ MOB'!B2:B99=SUMMERY!B4)*('30+
MOB'!M2:M99/MAX('30+ MOB'!K2:K99,1)))

prevents the #DIV/0. This assumes that the smallest non-zero in K will be
1. It can be made smaller if needed.

2) You can't do this for what you want to do. My formula  sums the results
of the divisions. I believe you are wanting to produce an average, which is
the division of the sums. So you can't simplify this for arithmetic
reasons, rather than for Excel technical reasons.

In other words, your formula is probably as simple as you're going to get

Didn't find what you were looking for? Find more on Please Help me in this Formula Or get search suggestion and latest updates.