Sumproduct Question

[Guest]

Is there a limit to the criterium that can be used in sumproduct?
Thanks!

 

[Guest]

In Excel 2003 or earlier, the limit is 30 arrays. In Excel 2007, the limit
is 255.

HTH,
Elkar

 

[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!

 

[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

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!

In Excel 2003 or earlier, the limit is 30 arrays. In Excel 2007, the limit
is 255.

HTH,
Elkar

 

[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)

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

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!

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!