• 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.

Use Formula to Count Unique Entries within Date Ranges

hurleystylee

New Member
I have data similar to the following:

[pre]
Code:
4/02/2011  abc
4/03/2011  xyz
4/03/2011  abc
4/10/2011  abc
4/11/2011  xyz
4/13/2011  qrs
I want to have static date ranges, use one formula to drag down the 'Unique' column:

[code]Start      End       Unique
4/01/2011  4/07/2011   2
4/08/2011  4/14/2011   3
[/pre]
So, I want a unique count WITHIN the date range. Currently I have this formula:

=SUM(1/COUNTIF(Data!$M$2:$M$10,Data!$M$2:$M$10))[/code]

But, I'm obviously not taking into account the date range. Currently, I have to hard-code the range to include the dates I'm looking for.
 
Hurleystylee


Firstly welcome to Chandoo.org Forums


This was a challenging one but I think I've knocked it off


I assume your:

Dates are in Column M

abc's are in Column N


I also have your Start Dates, End Dates and Unique in G2:I4

[pre]
Code:
Start		End		Unique
1/04/2011	7/04/2011	3
8/04/2011	14/04/2011	3
[/pre]
so in I3 put the following Array Formula


=SUM(N(FREQUENCY(IF(($M$2:$M$7>=G3)*($M$2:$M$7<=H3),MATCH($N$2:$N$7,$N$2:$N$7,0)),MATCH($N$2:$N$7,$N$2:$N$7,0))>0))


Enter this with Ctrl, Shift, Enter, not just Enter
 
Hi Hui,


I'm glad I could challenge the Ninja with my question! Usually I can do just about anything in Excel, but this had me stumped. Surprisingly in all my years of scouring the web for complicated Excel ideas, I've never came across this site. Now, I think it'll be the first place I go! Plus I've enjoyed perusing the content just for fun.


Getting back to the topic at hand, it worked!


Thank you!!
 
Back
Top