Hi all,
I want a formula for counting how many students have a maximum score < 30.
In this case (sheet attached) = 2. I will also apply it for more criteria
Q
Hi all,
I want a formula for counting how many students have a maximum score < 30.
In this case (sheet attached) = 2. I will also apply it for more criteria
Q
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind.
Which version of Excel are you using now?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
In F5 copied down:
=IFERROR(INDEX($A$2:$A$15,MATCH(0,INDEX(COUNTIF($F$4:F4,$A$2:$A$15),0,0),0)),"")
In G5 copied down (if you have a new version):
=IF(F5="","",MAXIFS($C$2:$C$15,$A$2:$A$15,F5))
or for an older version:
=IF(F5="","",MAX(IF($A$2:$A$15=F5,$C$2:$C$15)))
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
In I5:
=COUNTIF($G$5:$G$7,"<"&30)
Last edited by AliGW; 04-30-2021 at 03:15 AM.
Please try
=SUM(IFERROR(--(MOD(AGGREGATE(15,6,MATCH(A2:A15,A2:A15,)*10^6+C2:C15,ROW(A2:A15)-ROW(A1)/(A2:A15<>A3:A16)),10^6)<30),""))
Confirm with Ctrl+Shift+Enter
Please respond to post #2.
Its MS 365 - says Excel 2008. Feels like its a more recent version.
Please try
MS365
=SUM(--(MAXIFS($D$2:$D$50,$A$2:$A$50,UNIQUE(FILTER($A$2:$A$50,$B$2:$B$50=J$6)),$B$2:$B$50,J$6)<30))
older version
=SUM(IFERROR(--(MOD(AGGREGATE(15,6,MATCH($A$2:$A$50,$A$2:$A$50,)*10^6+$D$2:$D$50,ROW($A$2:$A$50)-ROW($A$1)/($A$2:$A$50<>$A$3:$A$51)/($B$2:$B$50=J$6)),10^6)<30),""))
Should this work when selecting the whole column?
Please put MS365 in your forum profile - 2008 is just a release number, not a year!Its MS 365 - says Excel 2008. Feels like its a more recent version.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks