Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
=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 |
#4
|
|||
|
|||
"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. |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal And Countif | Excel Discussion (Misc queries) | |||
countif | Excel Worksheet Functions | |||
Countif, Sumif, If - help! | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions | |||
combining countif formulas | Excel Worksheet Functions |