• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unique Dates

K

Kurt Smart

Member
Hi everyone,

I am trying to find how many unique dates there are in a column including a certain date from a cell

E.G. In my workbook there's a 'InProgress' tab and a 'Stats' tab.

In 'Stats' I want it to look in 'InProgress' and count me all the dates that are same as 10/10/2017 and before that date and give me the unique count of them.

Dates are in a table called 'InProgress' and a table header of 'Date Arrived'

Thank you for any help
 
Hi ,

Suppose we assume that the date 10/10/2017 is in cell Z1 ; then the following array formula , to be entered using CTRL SHIFT ENTER , may help.

=SUM(IF(InProgress[Date Arrived] <= $Z$1 , 1/COUNTIFS(InProgress[Date Arrived] , "<=" & $Z$1 , InProgress[Date Arrived] , InProgress[Date Arrived])))

Narayan
 
As array formulas tend to slow calculations down a helper column (say col B) containing =IF(COUNTIF($A$1:A1,$A1)>1,0,1) ( this column can be hidden if necessary)
The date count can then be obtained with =SUMIF($A$1:$A$100,"<="&Z1,$B$1:$B$100)
 
You must log in or register to reply here.
Back
Top