Count multiple instances within multiple row

[qwzky]

Hi! I am having some trouble with counting a certain value ("<5") within multiple rows as one, no matter how many times that value repeats itself:

SC-uri automate REDONE4 (test, ă văd dacă merge să lucrez in sheets separate).xlsm
	B	C	D	E	F	G	H	I
3	Students	Grade 1	Grade 2	Grade 3	Grade 4	Grade 5	GRADE 6	COUNT
4
5	Mark	4	4	7	7	5	5	count as 1, even if there are two "4"s (aka <=5)
6	John	6	6	6	7	6	6	don't count
7	Anne	abs	5	6	5	4	4	don't count, because there is at least 1 "abs"
8	James	10	10	10	10	9	5	don't count
9								the final result should be 2 (2 students failed)
CIORNĂ

The formula I tried is

=COUNTIF(C5:H8;"<5")

, but it returns 4 students failed. Please, help me make with this.

 

[Michael M]

How about

=IF(COUNTIF(C5:H5,"<5")>0,1,"")

drag down as required

 

[qwzky]

Hi, Michael! Now, it counts that only 1 students has failed. I need a formula that returns a single result that sums up every row somehow, but without creating a column withe separate results. Something like this:

SC-uri automate REDONE4 (test, ă văd dacă merge să lucrez in sheets separate).xlsm
	B	C	D	E	F	G	H	I
3	Students	Grade 1	Grade 2	Grade 3	Grade 4	Grade 5	GRADE 6	FAILED
4
5	Mark	4	4	7	7	5	5	1
6	John	6	6	6	7	6	6
7	Anne	abs	5	6	5	4	4
8	James	10	10	10	10	9	5
CIORNĂ

Cell Formulas
Range	Formula
I5	I5	=IF(COUNTIF(C5:H8,"<5")>0,1,"")

 

[Michael M]

Book1
	A	B	C	D	E	F	G	H	I
3	3	Students	Grade 1	Grade 2	Grade 3	Grade 4	Grade 5	GRADE 6	FAILED
4	4
5	5	Mark	4	4	7	7	5	5	1
6	6	John	6	6	6	7	6	6
7	7	Anne	abs	5	6	5	4	4	1
8	8	James	10	10	10	10	9	5
Sheet1

Cell Formulas
Range	Formula
I5:I8	I5	=IF(COUNTIF(C5:H5,"<5")>0,1,"")

 

[qwzky]

Michael, thank you for your fast reply I need a formula which should include the results in only one result. Something like this:

=IF(COUNTIF(C7:H7,"<5")>0,1,"")

But it doesn't work. Is it possible my request to be impossible to do in Excel?

 

[qwzky]

Sorry, I meant:

=SUM(IF(COUNTIF(C5:H5;"<5")>0;1;"");(IF(COUNTIF(C6:H6;"<5")>0;1;"");IF(COUNTIF(C7:H7;"<5")>0;1;"";IF(COUNTIF(C8:H8;"<5")>0;1;""))))

 

[qwzky]

This formula gave me "1"

 

[qwzky]

I am tired of this and this is why I always forget to double check my replies... I modified the last formula into

=IF(COUNTIF(C5:H5;"<5")>0;1;""+IF(COUNTIF(C6:H6;"<5")>0;1;""+IF(COUNTIF(C7:H7;"<5")>0;1;""+IF(COUNTIF(C8:H8;"<5")>0;1;""))))

It returns "1", instead of "2". Do you have a workaround? Also, if possible, a shorter formula, because I have 90 rows to count and it would take me some time to select all ranges.

 

[qwzky]

I will restart this thread because I made too many errors of judgement (hate my low-level brain)

 

[Michael M]

To be honest I would stick with my method and then put a sum at the bottom of column "I"