Excel Cell Value count
Closed
Dinesh
-
Aug 8, 2009 at 04:43 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 9, 2009 at 12:33 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 9, 2009 at 12:33 AM
Related:
- Excel Cell Value count
- How to count names in excel - Guide
- Excel marksheet - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Excel send value to another cell - Guide
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Aug 9, 2009 at 12:33 AM
Aug 9, 2009 at 12:33 AM
supposse the entries are in A1 and B1 try this formula
=(LEN(A1)-LEN(SUBSTITUTE(A1,"333",""))+LEN(B1)-LEN(SUBSTITUTE(B1,"333","")))/LEN("333")
now if there are more cells then it would be ccumbrsome. in that case use a macro
the macro is like this
Sub test()
Dim j As Integer, rng As Range, c As Range
j = 0
Set rng = Range(Range("a1"), Range("a1").End(xlToRight))
For Each c In rng
j = j + UBound(Split(c, "333", , 1))
Next c
MsgBox j
End Sub
This was suggested recently by Peter_SSs an expert in one of the newsgroups
=(LEN(A1)-LEN(SUBSTITUTE(A1,"333",""))+LEN(B1)-LEN(SUBSTITUTE(B1,"333","")))/LEN("333")
now if there are more cells then it would be ccumbrsome. in that case use a macro
the macro is like this
Sub test()
Dim j As Integer, rng As Range, c As Range
j = 0
Set rng = Range(Range("a1"), Range("a1").End(xlToRight))
For Each c In rng
j = j + UBound(Split(c, "333", , 1))
Next c
MsgBox j
End Sub
This was suggested recently by Peter_SSs an expert in one of the newsgroups
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Aug 8, 2009 at 08:18 PM
Aug 8, 2009 at 08:18 PM
I do not kow whether there are horizontal (in one row) A1 to G1 or in 7 rows from A1 to A7
if its former
=COUNTIF(A1:G1,"111")
will give you 2
if itis vertical
=COUNTIF(A1:A7,"111")
read excel help on "countif"
if its former
=COUNTIF(A1:G1,"111")
will give you 2
if itis vertical
=COUNTIF(A1:A7,"111")
read excel help on "countif"
