View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
JS SL JS SL is offline
external usenet poster
 
Posts: 49
Default Counts unique values based on 2 criteria in other columns(200.000 records)

Claus,

Thx !!!

I'd translated the formula to my sheet;
Column A=E, B=N, C=N. It works well :) but..... it still cost many many hours to calculate. Is there a faster way (the fact is that the sheet has 200.000 records)

=SUM(1/COUNTIF(OFFSET($E$2;MATCH(N2&Q2;$N$2:$N$200000&$Q$ 2:$Q$200000;0)-1;;COUNTIFS($N$2:$N$200000;N2;$Q$2:$Q$200000;Q2)); OFFSET($E$2;MATCH(N2&Q2;$N$2:$N$200000&$Q$2:$Q$200 000;0)-1;;COUNTIFS($N$2:$N$200000;N2;$Q$2:$Q$200000;Q2))) )

regards, johan