Any way to shorten this?

[theta]

Hi,

I have the following formula :

=SUMPRODUCT(--($A$10:$A$5000=A12),
((I12>$I$10:$I$5000)*(I12<$J$10:$J$5000))+
((J12>$I$10:$I$5000)*(J12<$J$10:$J$5000))+
((I12<$I$10:$I$5000)*(J12>$J$10:$J$5000))+
--(SUMPRODUCT(--($A$10:$A$5000=A12),--($I$10:$I$5000=I12))>1)+
--(SUMPRODUCT(--($A$10:$A$5000=A12),--($J$10:$J$5000=J12))>1))>0

Hoping to shorten it down into one SUMPRODUCT. Tried to nest 2 countifs but they do not respect the initial criteria --($A$10:$A$5000=A12)

Any ideas?

Regards

T

 

[the_original_invisible]

I haven't looked at your formula in any detail, but on the basis that you can put an or statement in to a sumproduct formula like this then I guess it should be possible

=SUMPRODUCT(--(A1:A10={2000,2001}))

Lee