how to count how many unique acc i have in a month

[AYSHANA]

good day

i want to count how many unique ACC i have in month

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ACC no.	UNIT ALLOCATED IN SYSTEM														2023
2	T788912	15-02-23 9:50		FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	TOTAL
3	W476272	15-02-23 14:44
4	W476272	15-02-23 14:44
5	T787716	14-02-23 17:38
6	T782689	16-02-23 10:56
7	W475023	15-02-23 23:51
8	H439472	17-02-23 8:58
9	S461054	19-02-23 9:40
10	S461054	19-02-23 9:40
11	S461054	01-04-23 8:00
12	S460113	18-02-23 19:44
13	S460113	18-02-23 19:44
14	S460113	18-02-23 19:44
15	S460113	01-0-2023 02:35
Sheet2

 

[AYSHANA]

Thanks, that is much clearer.

Yes, it is a reasonably similar formula but a bit simpler since it doesn't have to do any 'unique' assessment.
Note that I have changed the data in row 5 so that there is at least a count for March.

23 03 05.xlsm
	G			J							Q	R	S
1	CRA Receive Date Time			Workbench received to Result
2	14-02-23 16:47			31
3	14-02-23 13:54			30								2023
4	14-02-23 04:35			37
5	14-03-23 20:19			233							FEB	MAR	APR
6	14-02-23 16:05			31							2	1	0
7	14-02-23 14:01			36
8	14-02-23 14:10			31
9	14-02-23 10:32			110
10	14-02-23 02:34			38
11	14-02-23 12:39			30
12	14-02-23 16:36			39
13	14-02-23 20:44			39
14	14-02-23 05:19			42
15	14-02-23 14:18			64
Count >=60

Cell Formulas
Range	Formula
Q6:S6	Q6	=SUMPRODUCT(--(TEXT($G2:$G15,"mmmyyyy")=Q5&$R3),--($J2:$J15>=60))

Mr peter how are you again

i want to add two more criteria to the formula but im having difficulties,

i want to count the the orders that are >= 60 but not equal to #ERROR in the month

BGRPS STATISTICS.xlsm
	A	B					G			J			M	N	O	P	Q	R
1	No.	Orders					CRA Receive Date Time			Workbench received to Result
2	1	BGRPS					14-02-23 16:47			31
3	2	BGRPS					14-02-23 13:54			30								2023
4	3	BGRPS					14-02-23 4:35			37
5	4	BGRP					14-02-23 18:54			15						JAN	FEB	MAR
6	5	BGRPS					14-02-23 20:19			33					BGRPS		1
7	6	BGRPS					14-02-23 16:05			31					XM%		2
8	7	BGRPS					14-02-23 14:01			#ERROR
9	8	BGRPS					14-02-23 14:10			31
10	9	%XM					14-02-23 6:07			61
11	10	%XM					14-02-23 16:57			62
12	11	BGRP					14-02-23 1:45			53
13	12	BGRPS					14-02-23 10:32			110
14	13	%XM					14-02-23 12:57			30
raw daily

Cell Formulas
Range	Formula
A2	A2	=IF(ISBLANK(C2),"",1)
A3:A14	A3	=IF(C3="","",ROWS(A$2:A3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J304,J306:J3453	Cell Value	="#ERROR"	text	YES
J2:J304,J306:J3453	Cell	contains a blank value	text	YES
J2:J304,J306:J3453	Cell Value	>60	text	YES

 

[AYSHANA]

i dont want the formula to count for me the #ERROR , in the previous formula it was counting.

thank you

 

[Peter_SSs]

i want to add two more criteria

.. but you only described one.
I assume that the second one is that we have to take account of the values in column B matching the values in column O.
Speaking of column O, I also assume that your value in O7 was a typo and that value should be what I have in my O7 below?

23 03 05.xlsm
	B					G			J					O	P	Q	R
1	Orders					CRA Receive Date Time			Workbench received to Result
2	BGRPS					14-02-23 16:47			31
3	BGRPS					14-02-23 13:54			30								2023
4	BGRPS					14-02-23 04:35			37
5	BGRP					14-02-23 18:54			15						JAN	FEB	MAR
6	BGRPS					14-02-23 20:19			33					BGRPS		1	0
7	BGRPS					14-02-23 16:05			31					%XM		2	0
8	BGRPS					14-02-23 14:01			#ERROR
9	BGRPS					14-02-23 14:10			31
10	%XM					14-02-23 06:07			61
11	%XM					14-02-23 16:57			62
12	BGRP					14-02-23 01:45			53
13	BGRPS					14-02-23 10:32			110
14	%XM					14-02-23 12:57			30
15						14-02-23 14:18			64
Count >=60 (2)

Cell Formulas
Range	Formula
Q6:R7	Q6	=SUMPRODUCT(--($B$2:$B$15=$O6),--(TEXT($G$2:$G$15,"mmmyyyy")=Q$5&$R$3),--($J$2:$J$15>=60),--(ISNUMBER($J$2:$J$15)))

 

[AYSHANA]

yes there was a typo in O7 is should be %XM instead XM%

 

[Peter_SSs]

So, did the new formula do what you want?

 

[AYSHANA]

So, did the new formula do what you want?

yes it did

you saved me

thank you

 

[Peter_SSs]

OK, thanks for the confirmation.

 

[AYSHANA]

OK, thanks for the confirmation.

mr peter how are you
hope you are doing well

i would like to request your help again regarding how many counts i have in a month.

this time i would like to count (how many duplicate pt number I have in a day based on orders)

thank you again.

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Orders	PT Number	Result Date Time				HOW MANY DUPLICATE PT NUMBER I HAVE IN A DAY BASED ON ORDERS
2	BGRPS	11	3/1/2023 22:56							2023
3	BGRPS	12	3/1/2023 13:04
4	BGRP	12333	3/1/2023 20:24				ROLE	JAN	FEB	MAR	APR	MAY	JUN
5	BGRPS	123	3/1/2023 19:21				BGRPS WITH BGRPS
6	%XM	123	3/1/2023 21:53				BGRPS WITH %XM
7	BGRPS	3455	3/1/2023 16:24
8	BGRPS	5556	3/1/2023 21:22
9	BGRP	6677	3/1/2023 20:49
10	BGRPS	777	3/9/2023 16:57
11	BGRPS	234	3/9/2023 3:04
12	%XM	234	3/9/2023 20:54
13	BGRPS	666	3/10/2023 20:32
14	BGRPS	6655	3/10/2023 10:22
15	BGRPS	543	3/10/2023 6:36
16	BGRPS	245	3/10/2023 7:40
17	BGRPS	245	3/10/2023 6:43
18	BGRP	3445	3/10/2023 23:03
19	%XM	4556	3/10/2023 8:51
20	%XM	5554	3/1/2023 13:50
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	duplicates	text	NO