1

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: https://www.sendspace.com/file/u3hchq

How can I do it?

Even though I'm not on Excel 2010, please assume so.

1

The formula

=SUM(--(FREQUENCY(IF((C2:C30=O5)*(B2:B30=P3),MATCH(A2:A30,A2:A30,0)),ROW(A2:A30)-ROW(A2)+1)>0))

works.

  • 1
    This answer might get some upvotes — and, more importantly, will be more useful to other people — if you added an explanation of how and why it works. – Scott Sep 9 '17 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.