countif greater then and smaller then + if

[excelNewbie22]

hi,
whats wrong with my formula?
i need to know if the number of zeros in the range are greater then 0 and smaller then 5,
but as you can see it return TRUE for 6 zeros and also for no zeros

=COUNTIF(N19:S19,0)>0 - COUNTIF(N19:S19,0)>5

test.xlsb
	N	O	P	Q	R	S
17	TRUE
18	TRUE
19	0	0	0	0	0	0
20	1	1	1	2	2	1
test

Cell Formulas
Range	Formula
N17:N18	N17	=COUNTIF(N19:S19,0)>0 - COUNTIF(N19:S19,0)>5

 

[kweaver]

Simplistically, maybe this filled down from N17:

=AND(COUNTIF(N19:S19,0)>0,COUNTIF(N19:S19,0)<5)

 

[Arturthemaster]

Try to use countif, but you have to use aspas in criteria
=countif(N19:S19,">0""<6")

 

[awoohaw]

I'm not sure what you're counting. but here is a way to calculate numbers >0 and <5

mr excel questions 33.xlsm
	N	O	P	Q	R	S	T	U
3	0	0	0	0	0	0		0
4	1	1	1	2	2	1		6
ExcelNewbie22

Cell Formulas
Range	Formula
U3:U4	U3	=SUM((--(N3:S3>0))*(--(N3:S3<5)))

 

[Fluff]

Try to use countif, but you have to use aspas in criteria
=countif(N19:S19,">0""<6")

Have you actually tried that formula?

 

[excelNewbie22]

kweaver thanks you!
fluff was right, it doesn't works
awoohaw - didn't need to count bigger then zero, just if the amount of zeros are....
thanks everyone!

 

[Peter_SSs]

Another way would be

=ABS(COUNTIF(N19:S19,0)-2.5)<2