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]

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

Yes, blanks were being treated as zero.

 

[Lemon01]

Thank you so much! I never knew this; just thought excel would skip over blank cells.

 

[Lemon01]

If there is another column that I wanted to count using these same ranges but this column instead contains no blank cells but letters. Would I just use a simple countifs to come up with the number of A's, B's, Z's etc in each range?

=COUNTIFS(lookups!$E:$E,">="&'Dec 2013'!$B4,lookups!$E:$E,"<="&'Dec 2013'!$C4,lookups!$D:$D,S$3)

blue being my criteria range for the letter which is in always in row 3 but starts in column S. I don't need to worry about unique values of course.

 

[Andrew Poulsom]

Did you try it?

 

[AnnieD]

Dear

I have also another question concerning this formula.
Andrew, you already helped me very well with this data range.
But now I have want the formula to count the unique cells if the status in 1 colomn match multiply criteria.

This is my formula and this one is working. As you can see, the status in column D has to be `booked`.

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

But I also want him to count if the status is ´closed`.
So what I did now is just use + and add the same formula with status `closed´.

+SUM(--(FREQUENCY(IF(Data!$X:$X=$A2;IF(Data!$D:$D="CLOSED";IF(Data!$AH:$AH>="01/10/2011"+0;IF(Data!$AH:$AH<="30/09/2012"+0;Data!$O:$O))));Data!$O:$O)>0))

This also worked but I takes a lot of effort from Excell.

I was wondering if there is a better way to put this in one formula and will this speed up the calculation time in Excell?

Thank you again for you help!

 

[Aladin Akyurek]

Dear

I have also another question concerning this formula.
Andrew, you already helped me very well with this data range.
But now I have want the formula to count the unique cells if the status in 1 colomn match multiply criteria.

This is my formula and this one is working. As you can see, the status in column D has to be `booked`.

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

But I also want him to count if the status is ´closed`.
So what I did now is just use + and add the same formula with status `closed´.

+SUM(--(FREQUENCY(IF(Data!$X:$X=$A2;IF(Data!$D:$D="CLOSED";IF(Data!$AH:$AH>="01/10/2011"+0;IF(Data!$AH:$AH<="30/09/2012"+0;Data!$O:$O))));Data!$O:$O)>0))

This also worked but I takes a lot of effort from Excell.

I was wondering if there is a better way to put this in one formula and will this speed up the calculation time in Excell?

Thank you again for you help!

Do not reference whole columns; use definite ranges instead...

What do you have in column O, numbers?

 

[AnnieD]

Do not reference whole columns; use definite ranges instead...

What do you have in column O, numbers?

Ok, thank you for the tip!

In Column O are the ordernumbers so indeed numbers.

 

[Aladin Akyurek]

Ok, thank you for the tip!

In Column O are the ordernumbers so indeed numbers.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(
  IF(Data!$X$2:$X$200=$A2;
  IF(ISNUMBER(MATCH(Data!$D$2:$D$2000,{"BOOKED";"CLOSED"};0));
  IF(Data!$AH$2:$AH$2000>="01/10/2011"+0;
  IF(Data!$AH$2:$AH$2000<="30/09/2012"+0;
   Data!$O$2:$O$200))));Data!$O$2:$O$2000);1))

This will faster than summing the results associated with BOOKED and CLOSED obtained separately. However, it can still be heavy load. If the performance is unbearable, we can switch to a set up where we trade off memory against speed.

 

[Rasscal]

Hi

i know this is an old thread but im in desperate need of help

Im trying to do the same but with multiple criteria, as seen below.

If task = "PSHP" OR "SHIP"
what is the total number of orders for JAN

answer = 3

SHEET 1

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]MONTH[/TD]
[TD]NO. ORDERS[/TD]
[TD]START[/TD]
[TD]END[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]JAN[/TD]
[TD](RESULT HERE)[/TD]
[TD]01/01/19[/TD]
[TD]31/01/19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]FEB[/TD]
[TD][/TD]
[TD]01/02/19[/TD]
[TD]31/02/19[/TD]
[/TR]
</tbody>[/TABLE]

SHEET2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ORDER NO.[/TD]
[TD]TASK[/TD]
[TD]CREATED ON[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TT1234567[/TD]
[TD]SHIP[/TD]
[TD]01/01/19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TT1234567[/TD]
[TD]RPCK[/TD]
[TD]01/01/19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TT1234567[/TD]
[TD]PSHP[/TD]
[TD]02/01/19[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD](BLANK)[/TD]
[TD]CYCC[/TD]
[TD]01/01/19[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]TT1234567[/TD]
[TD]RPCK[/TD]
[TD]03/01/19[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]TT7654321[/TD]
[TD]RPCK[/TD]
[TD]10/01/19[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]TT9999999[/TD]
[TD]RPCK[/TD]
[TD]20/01/19[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]TT7654321[/TD]
[TD]SHIP[/TD]
[TD]25/01/19[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]TT8888888[/TD]
[TD]PSHP[/TD]
[TD]25/01/19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ETC 75,000+ ROWS [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


does anybody know how to add these criteria in?

thanks for looking

 

[Aladin Akyurek]

@ Rasscal

In B2 of Sheet1 control+shift+enter, not just,enter, and copy down:

=SUM(IF(FREQUENCY(IF(order<>"",IF(ISNUMBER(MATCH(task,{"PSHP","SHIP"},0)),IF(created-DAY(created)+1=$C2,MATCH(order,order,0)))),ROW(order)-ROW(INDEX(order,1,1))+1),1))

Note. The names refers to actual ranges, not to whole columns. Whole column references should be avoided.