Results 1 to 4 of 4
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    COUNTIF "H" and "h" (2000/2002)

    I'm trying to create a simple function that will add the occurances of the single letter "H" in a column:<pre>=(COUNTIF(J6:J42,"H"))</pre>

    Unfortunately (for me), the function is including occurrances of the lowercase - "h".
    (The ultimate aim is to get a function that will give me the sum of the number of H's and h's, where "H" = 1 and "h" = 0.5.)
    Could someone please nudge me in the right direction?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF "H" and "h" (2000/2002)

    This user defined function should give you what you want:

    <pre>Public Function GetCount(oVals As Range, strChr As String) As Long
    Dim lCount As Long, oCell As Range
    lCount = 0
    For Each oCell In oVals
    If oCell.Value = strChr Then
    lCount = lCount + 1
    End If
    Next oCell
    GetCount = lCount
    End Function
    </pre>


    In the sheet use:

    =GetCount(J6:J42,"H")
    Legare Coleman

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: COUNTIF "H" and "h" (2000/2002)

    Hi Leif,
    Just as an alternative to Legare's function, you could use:
    =SUM(IF(EXACT(J6:J42,"h"),1,0))
    array-entered.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTIF "H" and "h" (2000/2002)

    Perfect!
    Many thanks to you both. I got Legare's solution up and running first, so I'm quitting while I'm ahead. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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