Count Unique Values Repeated Dates

Question

I've got the following case I can't seem to figure out.

How to calculate the number of unique classes offered and done on specific dates.

| Dates | Class Number | Student Show |
| 02-06 | 1            | Y            |
| 02-06 | 1            | Y            |
| 02-06 | 5            | Y            |
| 03-06 | 10           | N            |
| 03-06 | 10           | N            |
| 03-06 | 3            | Y            |

Which would mean that on 02-06 there were 2 classes offered (1 and 5), both were done; and on 03-06 there were 2 offered but only 1 done.

Any help MUCH appreciated ;)

Answer 1

If you preferred to use a formula, you could use this one to find the number of classes for the current date:-

=SUM(IF(FREQUENCY(IF(A$2:A$7=A2,B$2:B$7),IF(A$2:A$7=A2,B$2:B$7))>0,1))

and this one to find the number of classes for which students turned up:-

=SUM(IF(FREQUENCY(IF((A$2:A$7=A2)*(C$2:C$7="y"),B$2:B$7),IF((A$2:A$7=A2)*(C$2:C$7="y"),B$2:B$7))>0,1))

See the notes here

Both must be entered as array formulae using Ctrl Shift Enter

Answer 2

Another solution:

Added new sheet: "Helper" - used "unique" function based on class code - vlookup to get class type and date - countifs to get number of classes offered and done - reference back from the stats sheet (where the summary table is) back to the unique list