Count Unique values between two dates

[AnnieD]

I want to count the unique values between 2 dates.
He only needs to count the values that match 2 criteria.
I want to know how many unique orders were place between 01/10/2012 and 30/09/2013.

In my Data sheet:
Colomn X: Customer name
Colomn D: Status
Colomn AH: Date of order (Day/Month/Year)

I want to count the unique values in
Colomn O: Ordernumbers

This is what I already have:

=SUM(--(FREQUENCY(IF(Data!$X:$X=$A4;IF(Data!$D:$D="BOOKED";Data!$O:$O));Data!$O:$O)>0))

This formula is working. He counts the unique values in colomn O that meet the 2 criteria (Status = "BOOKED"; Customer = A4).

But I don´t know how the add the date range in this formula.
I want him to take only the values that are between 01/10/2012 and 30/09/2013.

Can someone help me with this?

Thank you very much in advance!

 

[Andrew Poulsom]

Just add the other conditions:

=SUM(--(FREQUENCY(IF(Data!$X:$X=$A4,IF(Data!$D:$D="BOOKED",IF(Data!$AH:$AH>="01/10/2012"+0,IF(Data!$AH:$AH<="30/09/2013"+0,Data!$O:$O)))),Data!$O:$O)>0))

You can replace "01/10/2012"+0 and "30/09/2013"+0 with references to cells that contain the dates.

 

[AnnieD]

Just add the other conditions:

=SUM(--(FREQUENCY(IF(Data!$X:$X=$A4,IF(Data!$D:$D="BOOKED",IF(Data!$AH:$AH>="01/10/2012"+0,IF(Data!$AH:$AH<="30/09/2013"+0,Data!$O:$O)))),Data!$O:$O)>0))

You can replace "01/10/2012"+0 and "30/09/2013"+0 with references to cells that contain the dates.

Thank you very much Andrew!
This works indeed!
I already tried to just add the other conditions but I did´t add the +0
Can you tell why this is needed?

 

[Andrew Poulsom]

Adding zero converts the text into a serial date (a number).

 

[Lemon01]

Just add the other conditions:

=SUM(--(FREQUENCY(IF(Data!$X:$X=$A4,IF(Data!$D:$D="BOOKED",IF(Data!$AH:$AH>="01/10/2012"+0,IF(Data!$AH:$AH<="30/09/2013"+0,Data!$O:$O)))),Data!$O:$O)>0))

You can replace "01/10/2012"+0 and "30/09/2013"+0 with references to cells that contain the dates.

I've got what I think is the same problem. Here is the formula i'm using (different names of course):

=SUM(--(FREQUENCY(IF(BilldlOnly!$E$6:$E$518108>='Dec 2013'!$B4,IF(BilldlOnly!$E$6:$E$518108<='Dec 2013'!$C4,BilldlOnly!$J$6:$J$518108)),BilldlOnly!$J$6:$J$518108)>0))

'Dec 2013'!B4:B33 is my starting account number
'Dec 2013'!C4:C33 is my ending account number

Together these give my my account ranges. On the sheet 'billdlOnly' I want to count the unique number of orders in E6:E518108 based on those starting and ending ranges. The formula is working but I believe the counts are off. The sum of all of the counts for each range (30 total ranges) is 6,830 but if I went to sheet 'billdlOnly' and grab the range E6:E518108 and copy, remove duplicates, I only show 6,801 unique values. Could someone point me to what is incorrect with the formula i'm using?

 

[Andrew Poulsom]

What's on B4:C33 on worksheet 'Dec 2013'?

 

[Lemon01]

What's on B4:C33 on worksheet 'Dec 2013'?

Those are the starting and ending values of my ranges. Column B are the starting values and column C are the ending values of a range.

 

[Andrew Poulsom]

I understand that. Please post the actual data.

 

[Andrew Poulsom]

Does this return the expected result?

=SUM(--(FREQUENCY(IF(Lookups!$A$5:$A$518107>='Dec 2013'!$B4,IF(Lookups!$A$5:$A$518107<='Dec 2013'!$C4,IF(Lookups!$B$5:$B$518107<>"",Lookups!$B$5:$B$518107))),Lookups!$B$5:$B$518107)>0))

 

[Lemon01]

Does this return the expected result?

=SUM(--(FREQUENCY(IF(Lookups!$A$5:$A$518107>='Dec 2013'!$B4,IF(Lookups!$A$5:$A$518107<='Dec 2013'!$C4,IF(Lookups!$B$5:$B$518107<>"",Lookups!$B$5:$B$518107))),Lookups!$B$5:$B$518107)>0))

Perfect! So the last if just says in addition to being in this range, if it is not empty then count it?