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

 

[jsotola]

i think that i over thought this

here is a simpler way (less flexible though)

Sub test()

    Dim cel As Range


    Dim lo As Integer, mid As Integer, hi As Integer, max As Integer
    
    lo = 0: mid = 0: hi = 0: max = 0


    For Each cel In Range("g1:g8")
        Select Case cel
            Case 0 To 7
                lo = lo + 1
            Case 8 To 30
                mid = mid + 1
            Case 31 To 60
                hi = hi + 1
            Case Is > 60
                max = max + 1
            Case Else
                MsgBox "invalid number in cell"
        End Select
'        Debug.Print cel
    Next cel

    Debug.Print lo & vbTab & mid & vbTab & hi & vbTab & max 

End Sub

 

[howard]

Thanks for the help much appreciated. How would one compute this manually using countifs?

 

[jsotola]

i was just looking at that

countif allows a single"criteria range":"criteria" pair

countifs allows multiple "criteria range":"criteria" pairs (just do two different criteria on the same criteria range)

put these formulas into the four "result" cells

the two groups give the same result (the one on right is more legible, but it probably does more calculations)

=COUNTIFS(G[COLOR=#333333]1:G8[/COLOR],">=0",G[COLOR=#333333]1:G8[/COLOR],"< 8")                    =COUNTIFS(G[COLOR=#333333]1:G8[/COLOR],">= 0",G[COLOR=#333333]1:G8[/COLOR],"<= 7")
=COUNTIFS(G[COLOR=#333333]1:G8[/COLOR],"> 7",G[COLOR=#333333]1:G8[/COLOR],"<31")                    =COUNTIFS(G[COLOR=#333333]1:G8[/COLOR],">= 8",G[COLOR=#333333]1:G8[/COLOR],"<=30")
=COUNTIFS(G[COLOR=#333333]1:G8[/COLOR],">30",G[COLOR=#333333]1:G8[/COLOR],"<61")                    =COUNTIFS(G[COLOR=#333333]1:G8[/COLOR],">=31",G[COLOR=#333333]1:G8[/COLOR],"<=60")
 =COUNTIF(G[COLOR=#333333]1:G8[/COLOR],">60")                                 =COUNTIF(G[COLOR=#333333]1:G8[/COLOR],">=61")

 

[howard]

Thanks for the help, much appreciated