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

qwzky

New Member
Joined
Jul 22, 2021
Messages
33
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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
3GENDERStudentsGRADE 1GRADE 2GRADE 3GRADE4GRADE 5GRADE 6FAILED
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),"")
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,992
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKL
3GENDERStudentsGRADE 1GRADE 2GRADE 3GRADE4GRADE 5GRADE 6FAILED
4 
5MMark444655don't count, even if the row contains "4", because this is a male student0
6FHelen4abs5555don't count, because the row contains "abs", even if she is not a male and she has a "4"0
7MJohn665566don't count, because there is no "4" (aka <5) and bc this is a male student0
8FAnne7545443count as 1, even if there are 3 values of "4", because she is not a male student36
9FGwen545555count as 1, because she is not a male and because her row contains a "4"1
10MJames10104595don't count because he is a male student0
11FAlice10104694count as 1, even if there are 2 values of "4", because she is not male student2
Sheet1
Cell Formulas
RangeFormula
J4J4=IF(AND(H5>=6,MIN(C5:G5)>=5,COUNT(C5:G5)=COUNTA(C5:G5)),AVERAGE(C5:H5),"")
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"))
L8L8=SUM(K5:K11)
K5:K11K5=IF(AND(A5="F",ISNA(MATCH("abs",C5:H5,0))),COUNTIF(C5:H5,4),0)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

qwzky

New Member
Joined
Jul 22, 2021
Messages
33
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
K5:K11K5=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
@qwzky
In future, please do not post the same question multiple times. Per Forum Rules (#12).
 

Forum statistics

Threads
1,144,619
Messages
5,725,331
Members
422,617
Latest member
rahul27ragit

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top