counting unique dates

[allanon1]

I have a list of dates with some duplicates in it. I want to count how many dates there are without duplicates. Is there a simple formula to do so please.

Thanks in advance

 

[p45cal]

Sheet5

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:95px;"><col style="width:95px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td style="text-align:right; ">23/04/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td style="text-align:right; ">24/04/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td style="background-color:#ff99cc; color:#800000; text-align:right; ">02/05/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td style="text-align:right; ">26/04/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">21</td><td style="text-align:right; ">27/04/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">22</td><td style="background-color:#ff99cc; color:#800000; text-align:right; ">02/05/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">23</td><td style="text-align:right; ">29/04/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">24</td><td style="text-align:right; ">30/04/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">25</td><td style="text-align:right; ">01/05/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">26</td><td style="background-color:#ff99cc; color:#800000; text-align:right; ">02/05/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">27</td><td style="text-align:right; ">03/05/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">28</td><td style="text-align:right; ">04/05/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">29</td><td style="text-align:right; ">05/05/2010</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">30</td><td>
</td><td style="text-align:right; ">11</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B30</td><td>{=SUM(1/COUNTIF(A17:A29,A17:A29))}</td></tr></tbody></table></td></tr><tr><td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>
Note it's ARRAY-ENTERED

Excel tables to the web >> Excel Jeanie HTML 4

 

[Walky]

Hi,

Via a helper column, use this formula to count which occurrence of a certain date, each cell corresponds to out of the total number of similar dates... 1/5, 2/5, 1/3, 3/5, 2/3, etc. (dates 1, 2 & 4 are the same, as well as date 3 & 5).

Once done, all results starting with 1 will be the 1st occurrence of each date, so you simply need to create a filter for results begining with 1.

=COUNTIF(C$2:C3;C3)&"/"&COUNTIF(C:C;C3)

This formula, placed on line 3, is set for:
- header in cell C2
- 1st date in cell C3
- european version of Excel with ";" as a separator instead of "," in the US version


Based on the colour formatting in the previous post, I remembered that I also use part of the above formula in conditional formatting order to highlight occurrences that are greater than 1:

=COUNTIF(C:C;C3)>1

You can combine several conditional formats in order to have 1 colour if there are 2 occurrences and another if there are more than 2, leaving the 1st occurrenc without any format.

Good luck,

W.

 

[JEB85]

Say for example youR dates were in cells A1:A15;

=SUMPRODUCT(--(FREQUENCY(A$1:A$15,A$1:A$15)>0))