Single Conditional Array x two Multi-Column Array - Approach needed

[David Brown]

Hi - i am trying to multiply two arrays (multi-column) that are subject to a single column array. I know that SUMPRODUCT requires all arrays to be the same size. Does anyone know an alternative approach?


=SUMPRODUCT(--($F$9:$F$12="VDSL"),--($F$9:$F$12="GPON"),--($F$9:$F$12="PTP"),$AG$9:$AO$12,(1-$AS$9:$BA$12))

If the row does not satisfy a condition listed, then it is simply exclude from the multiplication and not added-up.

Any views on alternative approaches? Or do I need to write some VBA code? If so, does anyone know of some sample code that is similar and would need minimal adjustment so a non-VBA expert could implement?

Best / David

[DonkeyOte]

I know that SUMPRODUCT requires all arrays to be the same size

That's not strictly true.

It seems you're looking to conduct an OR based test re: F9 and then apply against some matrices

=SUMPRODUCT((($F$9:$F$12="VDSL")+($F$9:$F$12="GPON")+($F$9:$F$12="PTP"))*$AG$9:$AO$12*(1-$AS$9:$BA$12))

or

=SUMPRODUCT(ISNUMBER(MATCH($F$9:$F$12,{"VDSL","GPON","PTP"},0))*$AG$9:$AO$12*(1-$AS$9:$BA$12))

the latter approach is useful if you have a decent number of permissible values.

For more info. on SUMPRODUCT see the link in my sig. to Bob Phillips' white paper.

[David Brown]

Many thanks for this. I will see what I can do about getting you knighted by Queen Elizabeth.