Sumproduct OR

[trone77]

The following formula returns the #value error message:

=OR(SUMPRODUCT(--($C$2:$C$33=1),--($D$2:$D$33=4)>2,1,""),
SUMPRODUCT(--($C$2:$C$33=1),--($E$2:$E$33=6)>2,1,""),
SUMPRODUCT(--($D$2:$D$33=4),--($E$2:$E$33=6))>2,1,"")

Instead I need it to count if the string above occurs more than twice.

 

[Aladin Akyurek]

The following formula returns the #value error message:

=OR(SUMPRODUCT(--($C$2:$C$33=1),--($D$2:$D$33=4)>2,1,""),
SUMPRODUCT(--($C$2:$C$33=1),--($E$2:$E$33=6)>2,1,""),
SUMPRODUCT(--($D$2:$D$33=4),--($E$2:$E$33=6))>2,1,"")

Instead I need it to count if the string above occurs more than twice.

Do you mean...

=IF(OR(
    SUMPRODUCT(--($C$2:$C$33=1),--($D$2:$D$33=4))>2,
    SUMPRODUCT(--($C$2:$C$33=1),--($E$2:$E$33=6))>2,
    SUMPRODUCT(--($D$2:$D$33=4),--($E$2:$E$33=6))>2),1,"")

if not, would you elaborate on the the problem you want to solve in words?

 

[Norie]

Do the individual SUMPRODUCTS work?

It doesn't look to me as though they would.

Are you perhaps missing a closing parentheses for the SUMPRODUCT?

SUMPRODUCT(--($C$2:$C$33=1),--($D$2:$D$33=4))