Counbt Formula

[howard]

I would like a formula to count the number of units as follows based on the type in Col H

0-7
8-30
31-60
60 days plus

Your assistance in this regard is most appreciated

Excel Workbook
	C	D	E	F	G	H	I
3	KNB11102	HONDA JAZZ 1.5 EX	ZBD972KLZ	JHPXGE8880AS200169	47441501	U	15
4	KNB11134	FORD Figo 1.4 Sigma Ambiente	CJ20CKKLZ	PXAJ1LYPXRJ1CT59443	47441515	N	30
5	KNB11227	RENAULT LOGAN LOGAN 1.6 EXPRESSION	ZWN573KLZ	PXA1LSRADDAZD12508	47441516	N	45
6	KNB11117	MERCEDES-BENZ Mercedes C	RZS062KLZ	WDC2030422R185832	47441530	U	50
7	KNB11118	MERCEDES-BENZ Mercedes C	RZS062KLZ	WDC2030422R185833	47441531	U	51
8	KNB11119	MERCEDES-BENZ Mercedes C	RZS062KLZ	WDC2030422R185834	47441532	N	52
9	KNB11120	MERCEDES-BENZ Mercedes C	RZS062KLZ	WDC2030422R185835	47441533	U	53
10	KNB11121	MERCEDES-BENZ Mercedes C	RZS062KLZ	WDC2030422R185836	47441534	U	54
11
Sheet1

 

[Michael M]

Can you give us an idea of what results you expect on the sample data ?

 

[howard]

Hi Michael


Thanks for the reply

0-7 0
8-30 1
31-60 6
61+ 0

 

[Michael M]

Ok, you have 8 line items and you come up with 7 results ???
Are you counting based on the numbers in Col I and the letters in Col H ???



I think I need a drink.....

 

[jsotola]

add header row to data ( name the columns anything you like )

i chose column "I" to be named "num"

you can change to anything you like, you just have to make the namesame in the mySqlQuery string

Sub doSQL()

    Dim i As Integer
    
    Dim strCon As String
    Dim mySqlQuery As String
    
    ' refer to 'microsoft activex data objects library'
    Dim cn As Object
    Dim rs As Object
    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")


    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source='" & ThisWorkbook.FullName & "';" & _
             "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"


    cn.Open strCon
    
    mySqlQuery = "SELECT 0, count(1) FROM [Sheet1$A1:G10] WHERE  0 <= [B]num[/B] and [B]num[/B] <  8 UNION " & _
                 "SELECT 1, count(1) FROM [Sheet1$A1:G10] WHERE  7 <  [B]num[/B] and [B]num[/B] < 31 UNION " & _
                 "SELECT 2, count(1) FROM [Sheet1$A1:G10] WHERE 30 <  [B]num[/B] and [B]num[/B] < 61 UNION " & _
                 "SELECT 3, count(1) FROM [Sheet1$A1:G10] WHERE 60 <  [B]num[/B]              ORDER BY 1"
    
    rs.Open mySqlQuery, cn
        
    Sheets("Sheet1").Range("K2:N2").CopyFromRecordset rs
    
    rs.Close
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing


End Sub

 

[howard]

Hi Michael

Range 8-30 should be 2 NOT 1

Count on Col H

Regards

Howard

 

[howard]

Hi Jsotla

Thanks for the VBA Code

 

[jsotola]

you are welcome

i made an error in the sql command

FROM [Sheet1$A1:G10] should be FROM [Sheet1$C2:I11] in all four lines

i copied your data to "a1" instead of "c3" when i was testing

on your worksheet, row 2 should contain column names, therefore the table range is C2:I11

js

 

[jsotola]

you could also run four separate sql queries

    mySQLquery = "SELECT count(1) FROM [Sheet2$A1:G10] WHERE  0 <=  num and num < 8 "
    rs.Open mySQLquery, cn
    Debug.Print rs.Fields(0)
    rs.Close
    
    mySQLquery = "SELECT count(1) FROM [Sheet2$A1:G10] WHERE  7 <  num and num < 31 "
    rs.Open mySQLquery, cn
    Debug.Print rs.Fields(0)
    rs.Close
    
    mySQLquery = "SELECT count(1) FROM [Sheet2$A1:G10] WHERE  30 <  num and num < 61 "
    rs.Open mySQLquery, cn
    Debug.Print rs.Fields(0)
    rs.Close
    
    mySQLquery = "SELECT count(1) FROM [Sheet2$A1:G10] WHERE  60 <  num "
    rs.Open mySQLquery, cn
    Debug.Print rs.Fields(0)
    rs.Close

or use a function

Function countRange(cn As Object, w As String) As Integer
    
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")


    mySQLquery = "SELECT count(1) FROM [Sheet2$A1:G10] WHERE " & w
    rs.Open mySQLquery, cn
    countRange = rs.Fields(0)
    rs.Close


End Function

and call this way

    Debug.Print countRange(cn, "7 <  num and num < 31")

          abc = countRange(cn, "7 <  num and num < 31")

    sheets("Sheet1").Range("K4") = countRange(cn, "7 <  num and num < 31")

if you made the connection and recordset (cn & rs) objects global
then you would not have to pass then to the function


you could also pass only the hi and lo digits to the function and assemble the WHERE clause within the function

js

 

[jsotola]

i did a bit more research

if you do not have, or don't want column headers then change connection string HDR=No

    strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source='" & ThisWorkbook.FullName & "';" & _
             "Extended Properties='Excel 12.0;HDR=No;IMEX=1';"

the SQL engine assigns column names F1, F2, F3 ......

the query string becomes

mySQLquery = "SELECT count(1) FROM [Sheet2$A1:G10] WHERE  0 <=  F7 and F7 < 8 "