Distinct Value With Countif

[spalmarez]

Hi,
I am looking for help with distinct values using countif. This is
what I have so far. Please help.

A B
C
01/03/05 01/18/04 =Countif(A1:A14,"<="&B2) which equals 13 but I
want distinct values, which my value I want is 12
01/04/05
01/04/05
01/05/05
01/06/05
01/07/05
01/10/05
01/11/05
01/12/05
01/13/05
01/14/05
01/17/05
01/18/05
01/19/05

 

[Dave Peterson]

How about:

=SUMPRODUCT((A1:A14<=B2)/COUNTIF(A1:A14,A1:A14&""))

 

[Aladin Akyurek]

=SUM(--(FREQUENCY(IF(A2:A15<=B2,MATCH(A2:A15,A2:A15,0)),ROW(INDIRECT("1:"&ROWS(A2:A15)))-1)>0))

which you need to confirm with control+shift+enter instead of just wit
enter.

 

[Harlan Grove]

=SUM(--(FREQUENCY(IF(A2:A15<=B2,MATCH(A2:A15,A2:A15,0)),
ROW(INDIRECT("1:"&ROWS(A2:A15)))-1)>0))

....

=SUMPRODUCT((A2:A15<=B2)/COUNTIF(A2:A15,A2:A15))

may be more efficient, and it doesn't have to be entered as an array
formula.

 

[Aladin Akyurek]

....

=SUMPRODUCT((A2:A15<=B2)/COUNTIF(A2:A15,A2:A15))

may be more efficient, and it doesn't have to be entered as an array
formula.

Yeah. As long as the same range is involved, the formula should work,
something I tend to forget.