Unique count of student ids

Hi and welcome to the forum!

Try it like this, array formula**:

=SUM(IF(FREQUENCY(IF(($B2:$B111="Attended")+($B2:$B111="Assist"),IF($G2:$G111=$C112,MATCH(D2:D111,D2:D111,0))),ROW(D2:D111)-MIN(ROW(D2:D111))+1),1))

Regards


**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).

=SUM(IF(FREQUENCY(IF($B$2:$B$109="Assist",IF($D$2:$D$109="Attended",IF($G$2:$G$109=$C$112,MATCH($D$2:$D$109,$D$2:$D$109,0)))),ROW($D$2:$D$109)-ROW(A3)+1),1))

*array formula like you tried

Hi,

ROW(D2:D111)-MIN(ROW(D2:D111))+1 and ROW(D2:D111)-ROW(D2)+1 (which you will perhaps see used slightly more often these days) are constructions which generate an array of integers from 1 up to the number of rows in that range (i.e. in this case, from 1 to 110).

Although the first of these looks overly complicated in light of the second (and it does indeed have an extra function call, the MIN), I tend to use it, partly out of habit, but also as it has some merit in the fact that if, for example, we wanted to define our range, Range1 say, then this construction could be written:

ROW(Range1)-MIN(ROW(Range1))+1

whereas the second would still require one 'static' reference:

ROW(Range1)-ROW(D2)+1

(Unless we e.g. INDEXed the range in this version.)

Regards

=IFERROR(SUM(IF(FREQUENCY(IF($B$2:$B$109="Assist",IF($D$2:$D$109="Attended",IF($G$2:$G$109=$C$112,MATCH($D$2:$D$109,$D$2:$D$109,0)))),ROW($D$2:$D$109)-ROW(A3)+1),1)),"")