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 |
Sumproduct Error
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 |
All times are GMT +1. The time now is 09:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com