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

Excel Facts

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,200
Office Version
  1. 2016
Platform
  1. Windows
Hi Qwzky,

You could use a worker column.

Qwzky.xlsx
ABCDEFGHIJ
1
2
3GENDERStudentsGRADE 1GRADE 2GRADE 3GRADE4GRADE 5GRADE 6FAILED
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)
 
Solution

qwzky

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

Well-known Member
Joined
Mar 5, 2018
Messages
2,200
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
3GENDERStudentsGRADE 1GRADE 2GRADE 3GRADE4GRADE 5GRADE 6FAILED
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
Joined
Jul 22, 2021
Messages
33
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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
3GENDERStudentsGRADE 1GRADE 2GRADE 3GRADE4GRADE 5GRADE 6FAILED
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
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 22, 2021
Messages
33
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
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