I have a problem maybe some one can help. I have a list of numbers just one column wide and I want to be able to count how many 0 occur in the list and how many 1's occur in the list and so on up to the number of times a 9 occurs in the list. Can anyone suggest something.
Count the number of digits
-
-
-
Re: Count the number of digits
the =COUNTIF fuction will do what you require. Just do = COUNTIF([RANGE],[CRITERIA]) for example enter this into the bottom of a column
=COUNTIF(A1:A14,9)
This will return the number of nines in the range A1 to A14. Hope this helps.
-
Re: Count the number of digits
Matt boy, thanks for your reply, this was useful although it is not quite what I am after as this counts the number of times xzx occurs within a column of numbers but i want to know how many times x occurs and how many times z occurs within each field and then totalled for the column. So with xyx x=2 y=1. Hope this makes sense,
cheers
-
Re: Count the number of digits
I think the only way to do that would be with code.
Code
Display MoreSub CountDigits() Dim arrDigits(9) As Long Dim LastRow As Long Dim I As Long Dim AllNumbers As String Dim strMsg As String LastRow = Range("A65536").End(xlUp).Row For I = 1 To LastRow AllNumbers = AllNumbers & Range("A" & I) Next I For I = 1 To Len(AllNumbers) arrDigits(Mid(AllNumbers, I, 1)) = arrDigits(Mid(AllNumbers, I, 1)) + 1 Next I For I = 0 To 9 strMsg = strMsg & I & " occurs " & arrDigits(I) & " times" & vbCrLf Next I MsgBox strMsg End Sub
-
Re: Count the number of digits
Hi twi,
You can use the Sumproduct formula.
Code' Count the occurances of [b]1[/b] in the values contained in A2:A11 =SUMPRODUCT((LEN($A$2:$A$11)-(LEN(SUBSTITUTE($A$2:$A$11,[b]1[/b],"")))))
The attached shows the above formula extended to cover the digits 0-9
plus a more longwinded formula that does it for each row so you can double check its working. -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!