Hello all,
I'm looking for a method to count the number of unique values (ID Number) within a selected date range. I know how to count unique values within a general range (=SUM(IF(FREQUENCY($A:$A,$A:$A)>0,1)), but the additional variable of date range is stumping me something fierce.
Here's some demo data:
Please Login or Register to view this content.
e2=f2 =Please Login or Register to view this content.Then both drag down.Please Login or Register to view this content.
Then filter on column E on the values 1
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Try something like this...
F2 = start date
G2 = end date
Array entered**:
=SUM(IF(FREQUENCY(IF(A2:A20>=F2,IF(A2:A20<=G2,C2:C20)),C2:C20),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
You should avoid using entire columns as range references in array formulas. Use smaller specific ranges.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Try this...
Formula:
Please Login or Register to view this content.
Press Ctrl + shift + enter to enter the formula..
Don't forget to click *
Last edited by Vikas_Gautam; 08-15-2014 at 05:11 AM.
Re: Count Number of Unique Values within a Date Range
Check the attached file..
Dont' forget to click *
Last edited by Vikas_Gautam; 08-15-2014 at 06:02 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks