Distinct Value With Countif
 

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


--
spalmarez
------------------------------------------------------------------------
spalmarez's Profile: http://www.excelforum.com/member.php...o&userid=15961
View this thread: http://www.excelforum.com/showthread...hreadid=275769

How about:

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

spalmarez wrote:

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

--
spalmarez
------------------------------------------------------------------------
spalmarez's Profile: http://www.excelforum.com/member.php...o&userid=15961
View this thread: http://www.excelforum.com/showthread...hreadid=275769

--

Dave Peterson

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

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

spalmarez Wrote:
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


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275769

"Aladin Akyurek" wrote...
=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.

Harlan Grove Wrote:
"Aladin Akyurek" wrote...
=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.

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


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275769