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

#### qwzky

##### New Member
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
ABCDEFGHIJ
4
5MMark4446553don't count, even if the row contains "4", because this is a male student
6FHelen4abs5555don't count, because the row contains "abs", even if she is not a male and she has a "4"
7MJohn665566don't count, because there is no "4" (aka <5) and bc this is a male student
8FAnne754544count as 1, even if there are 3 values of "4", because she is not a male student
9FGwen545555count as 1, because she is not a male and because her row contains a "4"
10MJames10104595don't count because he is a male student
11FAlice10104694count as 1, even if there are 2 values of "4", because she is not male student
CIORNĂ
Cell Formulas
RangeFormula
J4J4=IF(AND(H5>=6,MIN(C5:G5)>=5,COUNT(C5:G5)=COUNTA(C5:G5)),AVERAGE(C5:H5),"")

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

##### Well-known Member
Hi Qwzky,

You could use a worker column.

Qwzky.xlsx
ABCDEFGHIJ
1
2
4
5MMark44465503
6FHelen4abs55550
7MJohn6655660
8FAnne7545441
9FGwen5455551
10MJames101045950
11FAlice101046941
Sheet1
Cell Formulas
RangeFormula
I5:I11I5=AND(A5="F",ISNA(MATCH("abs",C5:H5,0)),COUNTIF(C5:H5,"<5")>0)+0
J5J5=SUM(I5:I11)

#### qwzky

##### New Member
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

##### Well-known Member
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

##### Well-known Member

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

##### New Member
Got it now Thank you

#### jasonb75

##### Well-known Member

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
ABCDEFGHI
4
5MMark444655
6FHelen4abs5555
7MJohn665566
8FAnne7545443
9FGwen545555
10MJames10104595
11FAlice10104694
Sheet1
Cell Formulas
RangeFormula
I8I8=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

##### New Member
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
ABCDEFGHIJKLM
4 FAILEDHow many 4s have the students that failed?Sum
5MMark4446553don't count, even if the row contains "4", because this is a male student0don't include4
6FHelen4abs5555don't count, because the row contains "abs", even if she is not a male and she has a "4"0don't include
7MJohn665566don't count, because there is no "4" (aka <5) and bc this is a male student0don't include
8FAnne745555count as 1, even if there are 3 values of "4", because she is not a male student11
9FGwen545555count as 1, because she is not a male and because her row contains a "4"11
10MJames10104595don't count because he is a male student0don't include
11FAlice10104694count as 1, even if there are 2 values of "4", because she is not male student12
CIORNĂ
Cell Formulas
RangeFormula
J4J4=IF(AND(H5>=6,MIN(C5:G5)>=5,COUNT(C5:G5)=COUNTA(C5:G5)),AVERAGE(C5:H5),"")
I5I5=SUM(K5:K11)
M5M5=SUM(L5:L11)
K5:K11K5=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

##### Well-known Member
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

##### New Member
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.

Replies
11
Views
264
Replies
55
Views
608
Replies
8
Views
134
Replies
7
Views
146
Replies
1
Views
47

1,148,371
Messages
5,746,308
Members
424,006
Latest member
Metal_warrior

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back