Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Almas Akhtar   on Nov 07 In MS Office Category.

  
Question Answered By: Javairea Akram   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+
MOB'!K2:K99))

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
it.

Share: 

 

This Question has 1 more answer(s). View Complete Question Thread

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


Tagged: