| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Question
Is there a limit to the criterium that can be used in sumproduct?
Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct question | Excel Worksheet Functions | |||
| Sumproduct question | Excel Worksheet Functions | |||
| SumProduct Question | Excel Worksheet Functions | |||
| SumProduct question | Excel Discussion (Misc queries) | |||
| sumproduct question | Excel Worksheet Functions | |||
Linear Mode
