I'm using the formula
=SUM(IF(($C$2:$C$30=O5)*($B$2:$B$30=P3),1/COUNTIFS($C$2:$C$30,O5,$A$2:$A$30,$A$2:$A$30,$B$2:$B$30,P3)),0) to count how many distinct values on column A are such that their value on column B equals the value on cell P3 and their value on column C equals their value on cell O5. It basically is a count distinct on two criteria.
This is terribly inefficient for when the number of rows is large. I don't want to use pivot tables. I know there's a way to achieve the some result more efficiently using
FREQUENCY(), but I can't figure it out.
The file I'm playing with is located here:
How can I do it?
Even though I'm not on Excel 2010, please assume so.