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



" 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