Unique Values within Date Range

Question

I have Excel 2010 and need to either create a pivot or straight formula to count unique names within date ranges.  I have each name in Column D, and dates ranging over 6 months in Column E. (The table contains 3000+ records). I need to know how to create either a pivot table or just a formula that will return me how many uniques names are within each month (Dates are June - December 2014)

Thanks in advance for any idea.  I have been strugling to get this right for hours.

Answer 1

Hi,

Try this ARRAY formula. The formula looks at 2 dates in A1 and b1 and counts the unique values in column D where the date in Col E is >=A1 and <=B1

See below for how to enter an array formula. The formula currently does up to 1000 rows, if you increase this then all the range sizes must be the same.

=COUNT(1/FREQUENCY(IF($E$1:$E$1000>=A1,IF($E$1:$E$1000<=B1,IF($D$1:$D$1000<>"",MATCH($D$1:$D$1000,$D$1:$D$1000,0)))),ROW($D$1:$D$1000)-ROW($D$1)+1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter

and not just Enter. If you do it correctly then Excel will put curly brackets

around the formula {}. You can't type these yourself. If you edit the formula

you must enter it again with CTRL+Shift+Enter.

Answer 2

Hi,

Taking a cue from a similar problem solved here - http://www.ashishmathur.com/count-uniques-with-conditions/, try this.  In cell H2, enter this array formula (Ctrl+Shift+Enter) and copy down

=SUMPRODUCT(1*(FREQUENCY(IF(MONTH($D$2:$D$7)=$G2,,MATCH($E$2:$E$7,$E$2:$E$7,0)),ROW($E$2:$E$7)-ROW($E$1))>0))

Here's a screenshot

Answer 3

sorry, just realised you wanted count by month

pivot table might be neater, you can follow through the pivot wizard and then right click on the dates to group them into month-year

otherwise, you can change the dates and do $E2 and $D2 instead and copy that into the next 5 columns

=ISNA(MATCH(D2,$D$1:$D1,0))*AND($E2>=DATE(2014,6,1),$E2<=DATE(2014,12,31))

ideally, you may want to have the months set up somewhere else in 6 cells say P1:V1

Jun-14   Jul-14   Aug-14   Sep-14   Oct-14   Nov-14   Dec-14

Have F2:

=ISNA(MATCH(D2,$D$1:$D1,0))*AND(E2>=P$1,E2<=EOMONTH(P$1,0))

and copy F2 across to L2 and then down

P2: = SUM(F2:F999) copy across to V2

Answer 4

Just counting unique names.  Trying to get a monthly total.

Answer 5

Are you just counting unique names or are you listing what the names are too?

Counting only:

Column F:

F2: =ISNA(MATCH(D2,$D$1:D1,0))*AND(E2>=DATE(2014,6,1),E2<=DATE(2014,12,31)) - copy this down all the way

F1: = SUM(F2:F9999) gives you the total unique counts within the six months

List names:

F2: IF(ISNA(MATCH(D2,$D$1:D1,0))*AND(E2>=DATE(2014,6,1),E2<=DATE(2014,12,31)),D2,"") - copy down

F1: COUNT(F2:F9999)

You can even group all the names together in alphabetical order as a cluster if you want to do that, either in column F or in column G to avoid messy formula in F