Results 1 to 4 of 4
  1. #1
    lajos
    Guest

    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

  2. #2
    4 Star Lounger
    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)

  3. #3
    4 Star Lounger
    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.

  4. #4
    Uranium Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •