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

**David Brown** · 09-28-2010, 10:15 AM

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** · 09-28-2010, 10:22 AM

Originally Posted by David Brown

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

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

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.