G
Guest
Is there a limit to the criterium that can be used in sumproduct?
Thanks!
Thanks!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
In Excel 2003 or earlier, the limit is 30 arrays. In Excel 2007, the limit
is 255.
HTH,
Elkar
is 255.
HTH,
Elkar
G
Guest
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!
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!
G
Guest
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
=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 said: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 said:In Excel 2003 or earlier, the limit is 30 arrays. In Excel 2007, the limit
is 255.
HTH,
Elkar
G
Guest
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)
=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 said: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 said: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 said:In Excel 2003 or earlier, the limit is 30 arrays. In Excel 2007, the limit
is 255.
HTH,
Elkar
:
Is there a limit to the criterium that can be used in sumproduct?
Thanks!