Count if max < threshold

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

Which version of Excel are you using now?

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)

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

Originally Posted by Bo_Ry

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

Thanks! I need to adapt this with a criteria by year as well. Have reposted workbook

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?

Its MS 365 - says Excel 2008. Feels like its a more recent version.

Please put MS365 in your forum profile - 2008 is just a release number, not a year!