COUNTIFS doesn't work but IF works just fine

[Excel in Curiosity]

I have 3 cells with numbers E20 is 5, F20 is 2 and Y20 is 3.


In cell Z20:

=IF(Y20>F20,IF(Y20<E20,1,"")) gives me 1

However,

=COUNTIFS(Y20,">F20",Y20,"<E20") gives me 0



I can't figure out what happened. I have checked using isnumber() that all values are in fact numbers.

I need to use the countifs formula, please help.

 

[Habtest]

Welcome to the board!

Try:

=COUNTIFS(Y20,">"&F20,Y20,"<"&E20)

 

[Dave Patton]

try the syntax below

T202109a.xlsm
	E	F	G	H	I
20	5	2		1	1
4e

Cell Formulas
Range	Formula
H20	H20	=IF(Y20>F20,IF(Y20<E20,1,""))
I20	I20	=COUNTIFS(Y20,">"&F20,Y20,"<"&E20)

 

[Zot]

Should be written like this
=COUNTIFS(Y20,">" & F20,Y20,"<" & E20)

 

[anirudray]

Just make a little correction in your formula as stated below
=COUNTIFS(Y20,">"&F20,Y20,"<"&E20)

 

[Excel in Curiosity]

Hello, sorry for the late reply. I can't believe the speed you guys reply!
Thank you for all the replies. They work now thanks to you guys!!