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

	A	B	C	D
1	MONTH	NO. ORDERS	START	END
2	JAN	(RESULT HERE)	01/01/19	31/01/19
3	FEB		01/02/19	31/02/19

<tbody>
</tbody>

SHEET2

	A	B	C
1	ORDER NO.	TASK	CREATED ON
2	TT1234567	SHIP	01/01/19
3	TT1234567	RPCK	01/01/19
4	TT1234567	PSHP	02/01/19
5	(BLANK)	CYCC	01/01/19
6	TT1234567	RPCK	03/01/19
7	TT7654321	RPCK	10/01/19
8	TT9999999	RPCK	20/01/19
9	TT7654321	SHIP	25/01/19
10	TT8888888	PSHP	25/01/19
	ETC 75,000+ ROWS

<tbody>
</tbody>

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.