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),"")

 

[Toadstool]

Hi Qwzky,

You could use a worker column.

Qwzky.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2
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	0	3
6	F	Helen	4	abs	5	5	5	5	0
7	M	John	6	6	5	5	6	6	0
8	F	Anne	7	5	4	5	4	4	1
9	F	Gwen	5	4	5	5	5	5	1
10	M	James	10	10	4	5	9	5	0
11	F	Alice	10	10	4	6	9	4	1
Sheet1

Cell Formulas
Range	Formula
I5:I11	I5	=AND(A5="F",ISNA(MATCH("abs",C5:H5,0)),COUNTIF(C5:H5,"<5")>0)+0
J5	J5	=SUM(I5:I11)

 

[qwzky]

Hi, Toadstool! Thank you I will eventually use the worker. Isn't there any way to integrate those two formulas into one? I feel like it is possible to do so

 

[Toadstool]

Hi, Toadstool! Thank you I will eventually use the worker. Isn't there any way to integrate those two formulas into one? I feel like it is possible to do so

You're welcome!

I'm sure there's a VBA solution and maybe somebody smarter than I at Excel functions will be along later to offer you a single formula.

 

[jasonb75]

I feel like it is possible to do so

One thing that you will (hopefully) learn quickly is that there is a big difference between possible and practical when it comes to formulas.
As I pointed out in your original thread, it is possible, but the idea is as bad as trying to cut your nails with a chainsaw!

 

[qwzky]

Got it now Thank you

 

[jasonb75]

I'll show you the only method that will work for what you were asking for in a single cell. This will not be perfect, empty cells (among other things) will most likely cause errors.

Book1
	A	B	C	D	E	F	G	H	I
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
6	F	Helen	4	abs	5	5	5	5
7	M	John	6	6	5	5	6	6
8	F	Anne	7	5	4	5	4	4	3
9	F	Gwen	5	4	5	5	5	5
10	M	James	10	10	4	5	9	5
11	F	Alice	10	10	4	6	9	4
Sheet1

Cell Formulas
Range	Formula
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"))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[qwzky]

I totally understand what you meant and I can tell that I feel so small )
Now, I need another one: the same formula, but this time I want to know how many "4"s has each student, including the criteria I stated. It should go something like this:

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	K	L	M
3	GENDER	Students	GRADE 1	GRADE 2	GRADE 3	GRADE4	GRADE 5	GRADE 6	FAILED
4											FAILED	How many 4s have the students that failed?	Sum
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	0	don't include	4
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	don't include
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	don't include
8	F	Anne	7	4	5	5	5	5		count as 1, even if there are 3 values of "4", because she is not a male student	1	1
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	1
10	M	James	10	10	4	5	9	5		don't count because he is a male student	0	don't include
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	1	2
CIORNĂ

Cell Formulas
Range	Formula
J4	J4	=IF(AND(H5>=6,MIN(C5:G5)>=5,COUNT(C5:G5)=COUNTA(C5:G5)),AVERAGE(C5:H5),"")
I5	I5	=SUM(K5:K11)
M5	M5	=SUM(L5:L11)
K5:K11	K5	=AND(A5="F",ISNA(MATCH("abs",C5:H5,0)),COUNTIF(C5:H5,"<5")>0)+0

P.S. Is it ok if this new question appears here? Or must I create a new thread?

 

[jasonb75]

Your descriptions in column J don't match to the expected results that you have entered into columns L and M, which part is correct?

 

[qwzky]

In column L, the results (1, 1 and 2) should be the results of another formula (somehow altered) that counts how many 4s are there in total only for those students that have failed.