Counting unique dates in table

[sovietchild]

If I have 100 rows in the table and use =row() formula the end result will be 100 rows. However, what if I have 30 unique dates in the 100 rows, is there a formula that can distinctly count how many dates so far has been entered in the table? I uploaded a file for a better explanation.

[Graxon]

In C2, enter =COUNTA(UNIQUE($A$2:A2)) and drag the formula down. It should produce the same result as in your example in column B.

[CARACALLA]

C2=SUM(IF(FREQUENCY(IF(Table1[Date],Table1[Date]),Table1[Date]),1))

Control+shif+enter

[alansidman]

An alternative option is with Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[Bo_Ry]

Please try

=SUMPRODUCT(--(FREQUENCY(A$2:[@Date],A$2:[@Date])>0))

[sovietchild]

Please try

=SUMPRODUCT(--(FREQUENCY(A$2:[@Date],A$2:[@Date])>0))

Thanks this one worked.

[Czeslaw]

Formula

Please Login or Register  to view this content.