Results 1 to 4 of 4

20010316, 22:14 #1lajosGuest
Using Countif and Search functions
I need a formula that will return the number of times a piece of text appears in a range of cells. In this case the text consists of two letter combinations like WO, MA and the text in the cells can consist of a number of two letter combinations such as (WO,PI) or (YO, MA, RD).
I tried the following as an example:
=countif(a1:a10, search ("UD", "*"))
The result returned 0 even though there were cells in the range that included the text string. I used '*' as a wildcard argument which I thought would stand for any text string.
I consider myself a novice excel user, especially using functions. Any advice appreciated
Replies to: grantp@pl.net

20010317, 07:46 #2
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Using Countif and Search functions
Without using a VBA macro, I think you have to do it in two steps. Make an extra column and use the formula:
=ISERROR(FIND("WO";A1;1))
Here I assume that the range you are talking about is in Column A and that we put this formula in cell B1. This formula tries to finds "WO" in the cell A1 and returns FALSE if it is succesful and TRUE it not succesful. In fact FIND returns #VALUE if "WO" is not present; therefore I use ISERROR to return TRUE or FALSE. Copy this formula for your complete range and then use = COUNTIF("B1:B10";"FALSE")
Don't mention the semicolon ; in my formulas, this is probably a comma , in your case (has to do with my regional settings in Windows)

20010317, 07:52 #3
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Using Countif and Search functions
Now with VBA:
Paste this code in an empty module of the VBAproject attached to your worksheet:
Function Countabit(R As Range, PieceOfText As String) As Integer
Dim cell As Range
Dim n As Integer
For Each cell In R
If InStr(1, cell.Value, PieceOfText) Then
n = n + 1
End If
Next
Countabit = n
End Function
If you now use the functionwizard then you can find the function Countabit under the category User Defined. You enter the Range and the Piece of Text you want to count and this function will give you the result.

20010317, 09:59 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Using Countif and Search functions
The following array formula should do what you want to do. The formula assumes that the range you want to search is A1:A5, and that the string you want to search for is in B1. Those can be altered to fit your needs. This is an array formula, so it must be array entered. That means that you must hold down the Shift and Ctrl keys when you press the Enter key to enter the formula into the cell. If you do that correctly, Excel should put curly brackets {} around the formula.
<pre>=SUM(NOT(ISERROR(SEARCH(B1,A1:A5)))*1)
</pre>
Legare Coleman