How do I get an unduplicated count of names from a cell range, filtered by date?

I am creating a sheet summary for a sheet that includes a column called "School Name" and another column called "Program Inclusion Date." Some of the schools are listed more than once in the "School Name" column, and I am trying to get an unduplicated count of school names with dates in the "Program Inclusion Date" column <=TODAY(0). I am not having any luck.
The closest I have come so far are these two formulas:
=COUNT(DISTINCT([School Name]:[School Name]), COUNTIF([Program Inclusion Date]:[Program Inclusion Date], <=TODAY(0)))
which returns too many hits and
=COUNT(DISTINCT(COLLECT([School Name]:[School Name]), COUNTIF([Program Inclusion Date]:[Program Inclusion Date], <=TODAY(0))))
which returns only 1.
Can anyone help?
Comments
-
=count(distinct(collect([school name]:[school name],[program inclusion date]:[program inclusion date], @cell <= today()
Give that guy a try and see if it does what you want it to.
Help Article Resources
Categories
Check out the Formula Handbook template!