Ask your question
Message list Discussion Forum list

Excel Cell Value count

Dinesh - Aug 8, 2009 9:43am BST - Last answer on Aug 9, 2009 5:33am BST venkat1926
Report
I have questions

Can Cell determined by delimeter ?

I want to count the cell values how many 111 occurs ? pls refer the below example

ColumA Column B
111,222,333 222,333,444
666,333,111
Read more ...

More answers for :
Excel Cell Value count »

I have an answer
Add comment

Kioskea recommends you to read :

Excel formula to get the cell with the text [Solved]ForumOffice software
Macro help - search for text in ExcelForumOffice software
Excel IF statement with Data ValidationForumOffice software
Counting number of times a value appears if..ForumOffice software
Can vba return excel cell value with formatsForumOffice software
How to eneter values from user form to excelForumProgramming
Sort by :   Votes | Date | Date descending 4 answers
+0
moins plus
venkat1926 595Posts June 14, 2009Registration date March 22, 2010Last seen Aug 9, 2009 1:18am BST
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"

 Add comment - Permalink (1)
Dinesh - Aug 9, 2009 2:46am BST
Thanks Venkat.My requirement was column will have values like this " 111,222,333" and other column will have "444,555,333".I need to count how many 333 available in the two columns!!!!???

Is there a way ? Can you or any can help me ?
Dinesh › Dinesh - Aug 9, 2009 2:47am BST
Is there way to get the values from cell with delimeter?
Add comment
+0
moins plus
venkat1926 595Posts June 14, 2009Registration date March 22, 2010Last seen Aug 9, 2009 5:33am BST
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

 Add comment - Permalink (4)
Add comment
Top
See also
supinfo