|
Sumproduct Question
Is there a limit to the criterium that can be used in sumproduct?
Thanks! |
Sumproduct Question
In Excel 2003 or earlier, the limit is 30 arrays. In Excel 2007, the limit
is 255. HTH, Elkar "Sandy" wrote: Is there a limit to the criterium that can be used in sumproduct? Thanks! |
Sumproduct Question
OK Thanks. Alternatively I have my criteria' in book1 A3:A143. And I would
like my sumproduct formula in book2: =SUMPRODUCT(--($F$3:$F$500=7-F),--($J$3:$J$500=A),--($C$3:$C$500<70773128),--($C$3:$C$500<408435),--($C$3:$C$500<408436),--($C$3:$C$500<408437),--($C$3:$C$500<408438),k$3:k$500) I would like to add $C$3:$C$500<'[Book1.xls]Sheet1'!$A$3:$A$143 What is the proper structure for the formula? Thanks! "Elkar" wrote: In Excel 2003 or earlier, the limit is 30 arrays. In Excel 2007, the limit is 255. HTH, Elkar "Sandy" wrote: Is there a limit to the criterium that can be used in sumproduct? Thanks! |
Sumproduct Question
You could try something like:
=SUMPRODUCT(--($F$3:$F$500="7-F",--($J$3:$J$500="A"),--(NOT(ISERROR(MATCH($C$3:$C$500,'[Book1.xls]Sheet1'!$A$3:$A$143,0)))),$K$3:$K$500) HTH, Elkar "Sandy" wrote: OK Thanks. Alternatively I have my criteria' in book1 A3:A143. And I would like my sumproduct formula in book2: =SUMPRODUCT(--($F$3:$F$500=7-F),--($J$3:$J$500=A),--($C$3:$C$500<70773128),--($C$3:$C$500<408435),--($C$3:$C$500<408436),--($C$3:$C$500<408437),--($C$3:$C$500<408438),k$3:k$500) I would like to add $C$3:$C$500<'[Book1.xls]Sheet1'!$A$3:$A$143 What is the proper structure for the formula? Thanks! "Elkar" wrote: In Excel 2003 or earlier, the limit is 30 arrays. In Excel 2007, the limit is 255. HTH, Elkar "Sandy" wrote: Is there a limit to the criterium that can be used in sumproduct? Thanks! |
Sumproduct Question
I think NOT and ISERROR would give you the reverse result, try
=SUMPRODUCT(--($F$3:$F$500=7-F),--($J$3:$J$500=A),--ISNA(MATCH($C$3:$C$500,'[Book1.xls]Sheet1'!$A$3:$A$143,0)),K$3:K$500) "Elkar" wrote: You could try something like: =SUMPRODUCT(--($F$3:$F$500="7-F",--($J$3:$J$500="A"),--(NOT(ISERROR(MATCH($C$3:$C$500,'[Book1.xls]Sheet1'!$A$3:$A$143,0)))),$K$3:$K$500) HTH, Elkar "Sandy" wrote: OK Thanks. Alternatively I have my criteria' in book1 A3:A143. And I would like my sumproduct formula in book2: =SUMPRODUCT(--($F$3:$F$500=7-F),--($J$3:$J$500=A),--($C$3:$C$500<70773128),--($C$3:$C$500<408435),--($C$3:$C$500<408436),--($C$3:$C$500<408437),--($C$3:$C$500<408438),k$3:k$500) I would like to add $C$3:$C$500<'[Book1.xls]Sheet1'!$A$3:$A$143 What is the proper structure for the formula? Thanks! "Elkar" wrote: In Excel 2003 or earlier, the limit is 30 arrays. In Excel 2007, the limit is 255. HTH, Elkar "Sandy" wrote: Is there a limit to the criterium that can be used in sumproduct? Thanks! |
| All times are GMT +1. The time now is 07:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com