Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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+

MOB'!K2:K65536))

I want to make it in One Please Suggest.

Not sure if this simplified view makes sense:

=SUMPRODUCT(

(Array1=Condition1)

*(Array3=Condition3)

*(Array2=Condition2)

*(Array5)

)

/

SUMPRODUCT(

(Array1=Condition1)

*(Array3=Condition3)

*(Array2=Condition2)

*(Array4)

)

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:

=SUMPRODUCT(

(Array1=Condition1)

*(Array3=Condition3)

*(Array2=Condition2)

*(Array5)/(Array4)

)

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.

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

Related Topics:

- formula help but not vba please
- Can Any one help me writing logic for the given formula
- Paste Formula help
- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- How to add several conditions for formula "SUMIF"
- help newbie please this is driving me nuts
- Array Formula
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- Loop in formula
- Copy down formula with vba
- Stuck on copying formula from 1 sheet to another
- Named formula/Windows dialog boxes
- cells containing formula that refer to user-defined VBA function
- sum formula
- Blank Formula bar
- Manually creating a row outline with no formula anywhere
- Disabling Formulas, Not calculation
- Copy Value produced by formula in cell C3 to cell D3
- Help please
- Quotations in Formulas and strings
- 2003 - Moving to a formula address
- formula
- Any idea how to write formula?
- Formula with conditional text
- Constructing Address formulas