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]

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

 

[Peter_SSs]

No problem. Glad to help.

 

[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]

i have used this formula
but i dont know what wrong i did

=SUM(--(FREQUENCY(IF(YEAR($C2)&"-"&MONTH($C2)=YEAR($C$2:$C$200)&"-"&MONTH($C$2:$C$200), $B$2:$B$300, ""), ($B$2:$D$300))>0))

 

[Skyybot]

Try this. I added JAN before FEB on my sheet.

Sub accCount()
Dim myWbk As Workbook, mySht As Worksheet, accRng As Range, allRng As Range, myRng As Range
Dim i As Long, x As Long, y As Long
Dim accNbrJan, accNbrFeb, accNbrMar, accNbrApr, accNbrMay, accNbrJun, accNbrJul
Dim accNbrAug, accNbrSep, accNbrOct, accNbrNov, accNbrDec
Set myWbk = ThisWorkbook: Set mySht = myWbk.Worksheets("Sheet2")
Set accRng = mySht.Range(Cells(2, 1), Cells(mySht.UsedRange.Rows.Count, 1))
Set allRng = mySht.Range(Cells(2, 2), Cells(mySht.UsedRange.Rows.Count, 2))

For i = 2 To mySht.UsedRange.Rows.Count
    Select Case Month(mySht.Cells(i, 2))
        Case 1
            accNbrJan = accNbrJan + 1
        Case 2
            accNbrFeb = accNbrFeb + 1
        Case 3
            accNbrMar = accNbrMar + 1
        Case 4
            accNbrApr = accNbrApr + 1
        Case 5
            accNbrMay = accNbrMay + 1
        Case 6
            accNbrJun = accNbrJun + 1
        Case 7
            accNbrJul = accNbrJul + 1
        Case 8
            accNbrAug = accNbrAug + 1
        Case 9
            accNbrSep = accNbrSep + 1
        Case 10
            accNbrOct = accNbrOct + 1
        Case 11
            accNbrNov = accNbrNov + 1
        Case 12
            accNbrDec = accNbrDec + 1
        Case Else
            MsgBox "Not a valid date.", vbOKOnly, "Type Mismatch"
            Exit Sub
    End Select
Next i

mySht.Cells(3, 3) = accNbrJan: mySht.Cells(3, 4) = accNbrFeb: mySht.Cells(3, 5) = accNbrMar
mySht.Cells(3, 6) = accNbrApr: mySht.Cells(3, 7) = accNbrMay: mySht.Cells(3, 8) = accNbrJun
mySht.Cells(3, 9) = accNbrJul: mySht.Cells(3, 10) = accNbrAug: mySht.Cells(3, 11) = accNbrSep
mySht.Cells(3, 12) = accNbrOct: mySht.Cells(3, 13) = accNbrNov: mySht.Cells(3, 14) = accNbrDec


End Sub

 

[AYSHANA]

it is not working

 

[Skyybot]

it is not working

Edited for incorrect dates:

Sub accCount()
Dim myWbk As Workbook, mySht As Worksheet, accRng As Range, allRng As Range, myRng As Range
Dim i As Long, x As Long, y As Long
Dim accNbrJan, accNbrFeb, accNbrMar, accNbrApr, accNbrMay, accNbrJun, accNbrJul
Dim accNbrAug, accNbrSep, accNbrOct, accNbrNov, accNbrDec
Set myWbk = ThisWorkbook: Set mySht = myWbk.Worksheets("Sheet2")
Set accRng = mySht.Range(Cells(2, 1), Cells(mySht.UsedRange.Rows.Count, 1))
Set allRng = mySht.Range(Cells(2, 2), Cells(mySht.UsedRange.Rows.Count, 2))
On Error GoTo Err
For i = 2 To mySht.UsedRange.Rows.Count
    Select Case Month(mySht.Cells(i, 2))
        Case 1
            accNbrJan = accNbrJan + 1
        Case 2
            accNbrFeb = accNbrFeb + 1
        Case 3
            accNbrMar = accNbrMar + 1
        Case 4
            accNbrApr = accNbrApr + 1
        Case 5
            accNbrMay = accNbrMay + 1
        Case 6
            accNbrJun = accNbrJun + 1
        Case 7
            accNbrJul = accNbrJul + 1
        Case 8
            accNbrAug = accNbrAug + 1
        Case 9
            accNbrSep = accNbrSep + 1
        Case 10
            accNbrOct = accNbrOct + 1
        Case 11
            accNbrNov = accNbrNov + 1
        Case 12
            accNbrDec = accNbrDec + 1
    End Select
Next i

mySht.Cells(3, 3) = accNbrJan: mySht.Cells(3, 4) = accNbrFeb: mySht.Cells(3, 5) = accNbrMar
mySht.Cells(3, 6) = accNbrApr: mySht.Cells(3, 7) = accNbrMay: mySht.Cells(3, 8) = accNbrJun
mySht.Cells(3, 9) = accNbrJul: mySht.Cells(3, 10) = accNbrAug: mySht.Cells(3, 11) = accNbrSep
mySht.Cells(3, 12) = accNbrOct: mySht.Cells(3, 13) = accNbrNov: mySht.Cells(3, 14) = accNbrDec

Err:
    MsgBox "Check for invalid date.", vbOKOnly, "Type Mismatch"

End Sub

 

[Skyybot]

it is not working

I edited the code for invalid dates. Your row 15 has a month of 0?

 

[Skyybot]

I edited the code for invalid dates. Your row 15 has a month of 0?

Stand by....I need to change the code to count unique acc numbers. Sorry.