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

A

AYSHANA

Board Regular
Joined
Oct 16, 2021
Messages
90
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
good day

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

Book2
BCDEFGHIJKLMNOPQ
1ACC no.UNIT ALLOCATED IN SYSTEM2023
2T78891215-02-23 9:50FEBMARAPRMAYJUNJULAUGSEPOCTNOVDECTOTAL
3W47627215-02-23 14:44
4W47627215-02-23 14:44
5T78771614-02-23 17:38
6T78268916-02-23 10:56
7W47502315-02-23 23:51
8H43947217-02-23 8:58
9S46105419-02-23 9:40
10S46105419-02-23 9:40
11S46105401-04-23 8:00
12S46011318-02-23 19:44
13S46011318-02-23 19:44
14S46011318-02-23 19:44
15S46011301-0-2023 02:35
Sheet2
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
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))
 
Upvote 0
Try this. I added JAN before FEB on my sheet.

VBA Code: 
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
 
Upvote 0
AYSHANA said:
it is not working
Click to expand...
Edited for incorrect dates:
VBA Code: 
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
 
Upvote 0
=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)

i used this formula , but i dont know what wrong im doing

my data is big
 
Upvote 0
i try to do this but still

Book2
BCDEFGHIJKLMNOPQ
1ACC no.UNIT ALLOCATED IN SYSTEM2023
2T78891215-02-23 9:50janFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECTOTAL
3W47627215-02-23 14:44#DIV/0!
4W47627215-02-23 14:44
5T78771614-02-23 17:38
6T78268916-02-23 10:56
7W47502315-02-23 23:51
8H43947217-02-23 8:58
9S46105419-02-23 9:40
10S46105419-02-23 9:40
11S46105401-04-23 8:00
12S46011318-02-23 19:44
13S46011318-02-23 19:44
Sheet2
Cell Formulas
RangeFormula
E3E3=SUM(IF(1/COUNTIFS(B2:B11,B2:B11,$C$2:$C$11, ">="&DATEVALUE("1-"&Q$1&"-" & $E$2),$C$2:$C$11, "<"&EDATE(DATEVALUE("1-"&Q$1&"-" & $E$2),1))=1,1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Edited:
VBA Code: 
Sub accCount()
Dim myWbk As Workbook, mySht As Worksheet, accRng As Range, allRng As Range, myRng As Range, srchRng 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
Set srchRng = Range(accRng(i, 1), accRng(accRng.Rows.Count + 1, 1))
Set myRng = srchRng.Find(mySht.Cells(i, 1), LookAt:=xlWhole)
If myRng Is Nothing Then
    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
Else
    Debug.Print accRng.Find(mySht.Cells(i, 1))
End If
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Line chart only showing 4 dates on X-Axis
Replies
3
Views
261
awoohaw
awoohaw
L
  • Solved
How to get Login/Logout Times for users with multiple login/logouts on the multiple days
Replies
3
Views
199
leenvr
L
A
  • Solved
Returning name on multiple date arrays
Replies
9
Views
398
archibell
A
M
  • Question
Counting specific dates from multiple columns
Replies
2
Views
152
mattbird
M
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
248
Lukma
L

Forum statistics

Threads
1,215,356
Messages
6,124,471
Members
449,163
Latest member
kshealy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top