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

 

[Peter_SSs]

For your Excel versions, try this

23 03 05.xlsm
	B	C	D	E	F	G	H	I
1	ACC no.	UNIT ALLOCATED IN SYSTEM
2	T788912	15-02-23 09:50		FEB	MAR	APR	MAY	JUN
3	W476272	15-02-23 14:44		8	1	1	0	0
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 08:58
9	S461054	19-02-23 09:40
10	S461054	19-02-23 09:40
11	S461054	01-04-23 08: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-03-23 02:35
16
Count Unique

Cell Formulas
Range	Formula
E3:I3	E3	=SUMPRODUCT(--(TEXT($C2:$C15,"mmm")=E2),--(MATCH($B2:$B15&"|"&TEXT($C2:$C15,"mmm"),$B2:$B15&"|"&TEXT($C2:$C15,"mmm"),0)=ROW($B2:$B15)-ROW($B2)+1))

 

[Peter_SSs]

Sorry, I had missed Q1 and that apparently there needs to be a year check as well as a month check.

23 03 05.xlsm
	B	C	D	E	F	G	H	I							P	Q
1	ACC no.	UNIT ALLOCATED IN SYSTEM														2023
2	T788912	15-02-23 09:50		FEB	MAR	APR	MAY	JUN							TOTAL
3	W476272	15-02-23 14:44		8	1	1	0	0
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 08:58
9	S461054	19-02-23 09:40
10	S461054	19-02-23 09:40
11	S461054	01-04-23 08: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-03-23 02:35
16
Count Unique

Cell Formulas
Range	Formula
E3:I3	E3	=SUMPRODUCT(--(TEXT($C2:$C15,"mmmyyyy")=E2&$Q1),--(MATCH($B2:$B15&TEXT($C2:$C15,"|mmyy"),$B2:$B15&TEXT($C2:$C15,"|mmyy"),0)=ROW($B2:$B15)-ROW($B2)+1))

 

[AYSHANA]

Sorry, I had missed Q1 and that apparently there needs to be a year check as well as a month check.

23 03 05.xlsm
	B	C	D	E	F	G	H	I							P	Q
1	ACC no.	UNIT ALLOCATED IN SYSTEM														2023
2	T788912	15-02-23 09:50		FEB	MAR	APR	MAY	JUN							TOTAL
3	W476272	15-02-23 14:44		8	1	1	0	0
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 08:58
9	S461054	19-02-23 09:40
10	S461054	19-02-23 09:40
11	S461054	01-04-23 08: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-03-23 02:35
16
Count Unique

Cell Formulas
Range	Formula
E3:I3	E3	=SUMPRODUCT(--(TEXT($C2:$C15,"mmmyyyy")=E2&$Q1),--(MATCH($B2:$B15&TEXT($C2:$C15,"|mmyy"),$B2:$B15&TEXT($C2:$C15,"|mmyy"),0)=ROW($B2:$B15)-ROW($B2)+1))

thank you

 

[Peter_SSs]

You're welcome.

 

[AYSHANA]

You're welcome.

I have another question, suppose I have the same (mmmyyy) column and i want to count if its >=60
i can use the same function or it will be different

thanks again

CRA Receive Date Time	Workbench received to Result
14/02/2023 16:47	31
14/02/2023 13:54	30
14/02/2023 04:35	37
14/02/2023 20:19	33
14/02/2023 16:05	31
14/02/2023 14:01	36
14/02/2023 14:10	31
14/02/2023 10:32	110
14/02/2023 02:34	38
14/02/2023 12:39	30
14/02/2023 16:36	39
14/02/2023 20:44	39
14/02/2023 05:19	42
14/02/2023 14:18	64
14/02/2023 14:15	34
14/02/2023 16:43	40
14/02/2023 14:10	77
14/02/2023 14:07	31

 

[Peter_SSs]

Not enough detail to be sure I am understanding your requirement. What about sample data and expected results with XL2BB and explain again in relation to that?

 

[AYSHANA]

i want to count how many times i got a result that is >=60 in month

COMBINE vba .xlsm
	G			J							Q	R	S
1	CRA Receive Date Time			Workbench received to Result
2	14/02/2023 16:47			31
3	14/02/2023 13:54			30								2023
4	14/02/2023 04:35			37
5	14/02/2023 20:19			33							FEB	MAR	APR
6	14/02/2023 16:05			31							2
7	14/02/2023 14:01			36
8	14/02/2023 14:10			31
9	14/02/2023 10:32			110
10	14/02/2023 02:34			38
11	14/02/2023 12:39			30
12	14/02/2023 16:36			39
13	14/02/2023 20:44			39
14	14/02/2023 05:19			42
15	14/02/2023 14:18			64
raw daily

 

[Peter_SSs]

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))

 

[AYSHANA]

I understand, thanks a lot

 

[Peter_SSs]

No problem. Glad to help.