Sumproduct Error

Hi All,

I am trying to get a sumproduct formula to work and can't figure it
out. These two formulas work:

=SUMPRODUCT((MODEL_DATA!M2:M65536)*(MODEL_DATA!L2:L65536=1)*(MODEL_DATA!K2:K65536=1)*(MODEL_DATA!D2:D65536=IF(MODEL_SETUP!C7<>0,MODEL_SETUP!C7,{1,2,3,4,5,6})))

=SUMPRODUCT((MODEL_DATA!M2:M65536)*(MODEL_DATA!L2:L65536=1)*(MODEL_DATA!K2:K65536=1)*(MODEL_DATA!E2:E65536=IF(MODEL_SETUP!C8<>0,MODEL_SETUP!C8,{1,2})))

But if I combine the last criteria of both, it causes an #N/A error:

=SUMPRODUCT((MODEL_DATA!M2:M65536)*(MODEL_DATA!L2:L65536=1)*(MODEL_DATA!K2:K65536=1)*(MODEL_DATA!D2:D65536=IF(MODEL_SETUP!C7<>0,MODEL_SETUP!C7,{1,2,3,4,5,6}))*(MODEL_DATA!E2:E65536=IF(MODEL_SETUP!C8<>0,MODEL_SETUP!C8,{1,2})))

For the last two criteria, basically what I what it to do is if the
lookup value = 0, return 1 for all rows, but if it equals another
value, return 1 only if the value in the range matches. I can't figure
out how to get both criteria to work in one formula, any suggestions on
fixing the error or a simpler way of doing the calculation would be
appreciated.

Michael Card

That is because the 1st criteria results in a (n x 6) array and you try to
multiply the results of the second criteria which is a (nx2) array. This
results in a (n x 6) array with the last four columns all filled with N/A.

This is of course when both the false conditions kick in applying the Array
constants.

--
Regards,
Tom Ogilvy



"[email protected]" wrote:

> Hi All,
>
> I am trying to get a sumproduct formula to work and can't figure it
> out. These two formulas work:
>
> =SUMPRODUCT((MODEL_DATA!M2:M65536)*(MODEL_DATA!L2:L65536=1)*(MODEL_DATA!K2:K65536=1)*(MODEL_DATA!D2:D65536=IF(MODEL_SETUP!C7<>0,MODEL_SETUP!C7,{1,2,3,4,5,6})))
>
> =SUMPRODUCT((MODEL_DATA!M2:M65536)*(MODEL_DATA!L2:L65536=1)*(MODEL_DATA!K2:K65536=1)*(MODEL_DATA!E2:E65536=IF(MODEL_SETUP!C8<>0,MODEL_SETUP!C8,{1,2})))
>
> But if I combine the last criteria of both, it causes an #N/A error:
>
> =SUMPRODUCT((MODEL_DATA!M2:M65536)*(MODEL_DATA!L2:L65536=1)*(MODEL_DATA!K2:K65536=1)*(MODEL_DATA!D2:D65536=IF(MODEL_SETUP!C7<>0,MODEL_SETUP!C7,{1,2,3,4,5,6}))*(MODEL_DATA!E2:E65536=IF(MODEL_SETUP!C8<>0,MODEL_SETUP!C8,{1,2})))
>
> For the last two criteria, basically what I what it to do is if the
> lookup value = 0, return 1 for all rows, but if it equals another
> value, return 1 only if the value in the range matches. I can't figure
> out how to get both criteria to work in one formula, any suggestions on
> fixing the error or a simpler way of doing the calculation would be
> appreciated.
>
> Michael Card
>
>