I have problem to solve,I have a column with office. The text in the Office cells with be, Office 1@810@NT but I now have a new columm with text in the following format:Test The Cell - Employment cells can contain mutiple values, Test The Cell - Education;Test The Cell - Employment;Test The Cell - Health
. These values are separated by a ;
NOt only do I need to count each occurance, but I then need to have a count of each one. I can have these values referred to in a cell if that makes it easier. Sorry if I have not been clear


Submitted via EggHeadCafe - Software Developer Portal of Choice
The SQLite Database Engine and ADO.NET Provider
http://www.eggheadcafe.com/tutorials...ase-engin.aspx

This should work where abc is you search string and A1:A100 is your
column

=SUMPRODUCT(--(ISERROR(FIND("abc",A1:A100))=FALSE))


Find will return an error if the string is not found. You want to count
the number of occurances that are NOT Errors


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170383

Microsoft Office Help

Hi Joel, thanks for your response,

The only problem with this, is that a cell can contain "abc" and other text within the cell. Example:

Cell A1, can contain the following, abc;somthingelse;abc;again somthing else;abc

The formula you supplied will only count "abc" once in this cell. The result in the examble above you be 3.



joel wrote:

This should work where abc is you search string and A1:A100 is
15-Jan-10

This should work where abc is you search string and A1:A100 is your
column

=SUMPRODUCT(--(ISERROR(FIND("abc",A1:A100))=FALSE))


Find will return an error if the string is not found. You want to count
the number of occurances that are NOT Errors


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170383

Microsoft Office Help

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET Programmer's Reference [Wrox Press]
http://www.eggheadcafe.com/tutorials...mers-refe.aspx

I went to Chip Pearson's webpage and found a solution using the
worksheetfunction REPLACE but it only worked on one cell.

'String Formulas' (http://www.cpearson.com/Excel/stringformulas.aspx)

=IF(LEN(B1)=0,0,(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1))

The function wouldn't work with a formula array. So it gave me the
idea to use a UDF function. Call thsi function like this


=CountString(A1:A100,"abc")

or

=CountString(A1:A100,bi)


Function CountString(Target, Pattern)

CountString = 0
For Each Cell In Target
MyString = Cell
Do While InStr(MyString, Pattern) 0
CountString = CountString + 1
MyString = Replace(expression:=MyString, _
Find:=Pattern, Replace:="", Start:=1, Count:=1)
Loop
Next Cell

End Function


The function keeps on replacing the search string with nothing until
the string isn't found any more counting the number of times the
replaement is done.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170383

Microsoft Office Help