Count rows that contain a value, regardless of number of times the value repeats itself in every cell, but with condition

[qwzky]

Hi! I am having some trouble with counting a certain value ("<5") within each row as one, no matter how many times that value repeats itself in every row (I want to be able to tell how many girls have failed the year). Then sum up all those counts, within the same formula. Add two criteria to exclude (from count) the rows that contain one or more cells with "abs" value and also exclude male students (noted as "M"), even if the row contains the original value ("<5"). The whole result should appear in only one cell, as below, because I want to be able to tell how many girls have failed the year:

SC-uri automate REDONE4 (test, ă văd dacă merge să lucrez in sheets separate).xlsm
	A	B	C	D	E	F	G	H	I	J
3	GENDER	Students	GRADE 1	GRADE 2	GRADE 3	GRADE4	GRADE 5	GRADE 6	FAILED
4
5	M	Mark	4	4	4	6	5	5	3	don't count, even if the row contains "4", because this is a male student
6	F	Helen	4	abs	5	5	5	5		don't count, because the row contains "abs", even if she is not a male and she has a "4"
7	M	John	6	6	5	5	6	6		don't count, because there is no "4" (aka <5) and bc this is a male student
8	F	Anne	7	5	4	5	4	4		count as 1, even if there are 3 values of "4", because she is not a male student
9	F	Gwen	5	4	5	5	5	5		count as 1, because she is not a male and because her row contains a "4"
10	M	James	10	10	4	5	9	5		don't count because he is a male student
11	F	Alice	10	10	4	6	9	4		count as 1, even if there are 2 values of "4", because she is not male student
CIORNĂ

Cell Formulas
Range	Formula
J4	J4	=IF(AND(H5>=6,MIN(C5:G5)>=5,COUNT(C5:G5)=COUNTA(C5:G5)),AVERAGE(C5:H5),"")

 

[jasonb75]

If you're re-using existing example then it would be a good idea to remove any formulas / descriptions that relate to the previous question and only show is what is relevant.

Is this what you want?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
3	GENDER	Students	GRADE 1	GRADE 2	GRADE 3	GRADE4	GRADE 5	GRADE 6	FAILED
4
5	M	Mark	4	4	4	6	5	5		don't count, even if the row contains "4", because this is a male student	0
6	F	Helen	4	abs	5	5	5	5		don't count, because the row contains "abs", even if she is not a male and she has a "4"	0
7	M	John	6	6	5	5	6	6		don't count, because there is no "4" (aka <5) and bc this is a male student	0
8	F	Anne	7	5	4	5	4	4	3	count as 1, even if there are 3 values of "4", because she is not a male student	3	6
9	F	Gwen	5	4	5	5	5	5		count as 1, because she is not a male and because her row contains a "4"	1
10	M	James	10	10	4	5	9	5		don't count because he is a male student	0
11	F	Alice	10	10	4	6	9	4		count as 1, even if there are 2 values of "4", because she is not male student	2
Sheet1

Cell Formulas
Range	Formula
J4	J4	=IF(AND(H5>=6,MIN(C5:G5)>=5,COUNT(C5:G5)=COUNTA(C5:G5)),AVERAGE(C5:H5),"")
I8	I8	=SUMPRODUCT((SUBTOTAL(2,OFFSET($C$5,ROW($C$5:$C$11)-ROW($C$5),0,1,COLUMNS($C$5:$H$5)))=SUBTOTAL(3,OFFSET($C$5,ROW($C$5:$C$11)-ROW($C$5),0,1,COLUMNS($C$5:$H$5))))*(SUBTOTAL(5,OFFSET($C$5,ROW($C$5:$C$11)-ROW($C$5),0,1,COLUMNS($C$5:$H$5)))<=4)*($A$5:$A$11="F"))
L8	L8	=SUM(K5:K11)
K5:K11	K5	=IF(AND(A5="F",ISNA(MATCH("abs",C5:H5,0))),COUNTIF(C5:H5,4),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[qwzky]

K5:K11	K5	=IF(AND(A5="F",ISNA(MATCH("abs",C5:H5,0))),COUNTIF(C5:H5,4),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Yes! I love the fact you mentioned about ctrl+shift+enter I am so happy right now Thank you, Jason, and also thank you, Toadstool!

 

[Fluff]

@qwzky
In future, please do not post the same question multiple times. Per Forum Rules (#12).

 

[Toadstool]

...also thank you, Toadstool!

You're welcome!
...and congratulations Stumpy on the SUMPRODUCT/SUBTOTAL solution

 

[jasonb75]

the SUMPRODUCT/SUBTOTAL solution

Personally, I would say that your method with the helper column is a solution, my method is just a messy formula that works ?