Countifs on 3 conditions

[rex759]

Hello,
I've been using the formula

=IF(SUMPRODUCT(--($A$2:$A$2000>A2-15),--($A$2:$A$2000<A2+15),--($S$2:$S$2000=S2))>3,"Y","N")

and want to switch using Countifs to speed up the calculation.

I tried

=IF(COUNTIFS($A$2:$A$2000,">a2-15”,$A$2:$A$2000,"<A2+15”,$S$2:$S$2000,S2)>3,"Y","N")

but it didn't give me the correct results.


Any ideas why?

Thank you

 

[schielrn]

Try putting spaces before and after your < and > because I believe the board is cutting off some of your formula due to the way it renders those and thinks they are html code.

 

[rex759]

How strange.
This was my original post

Change Sumproduct
IF(SUMPRODUCT(--($A$2:$A$2000 > A2-15),--($A$2:$A$2000 < A2+15),--($S$2:$S$2000=S2)) > 3,"Y","N")

to

Countifs

IF(COUNTIFS($A$2:$A$5," > a2-15”,$A$2:$A$5," < A2+15”,$S$2:$S$5,S2) > 3,"Y","N")




This seems to work.


IF(COUNTIFS($A$2:$A$2000,">"&A2-15,$A$2000:$A$5,"<"&A2+15,$S$2000:$S$5,"="&S2)>3,"Y","N")