Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CountIf problem (2003 SP3)

    Good morning

    I am currently using a number of CounIf formulas to add up the occurence of a letter in fields B5 - AQ5 and that is not a problem, what I would like to do though is to try and use the CountIf to add up H = 1 and h = 0.5 and still give me a total for instance if in the range B5 - AQ5 I had H occuring 5 times and h occuring 3 times the CountIf would = 6.5

    Any ideas please

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    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 problem (2003 SP3)

    Steve,
    Is the 'H' or 'h' the sole contents of the cell, or are you counting any appearance of those letters within the cell contents? If they are the sole contents, then you can use:
    <code>=SUMPRODUCT(--EXACT($B$5:$AQ$5,"H"))</code>

    otherwise you would need a UDF I think - something like:
    <pre>Public Function CountIfExact(rngInput As Range, strCriteria As String) As Long
    Dim rngCell As Range, lngCounter As Long
    For Each rngCell In rngInput
    If rngCell.Text Like strCriteria Then lngCounter = lngCounter + 1
    Next rngCell
    CountIfExact = lngCounter
    End Function
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIf problem (2003 SP3)

    Thanks Rory

    There are a number of letters involved that at the moment get counted into different cells, these are H (Holiday), S (Sickness) etc. and I have been asked to further break down leave days into 1/2 days or full days, I thought that the easiest way would be a Capital representing a Full Day and a Lower case representing a 1/2 day.

    Can you please exaplain in your first suggestion - =SUMPRODUCT(--EXACT($B$5:$AQ$5,"H")) how that would determine H=1 or h=0.5, also what are the -- before EXact for?

    I have never used UDF's so if possible I will avoid them for now as I cannot understand 1 part of your example as it does not mention H or h or 1 or 0.5 in it anywhere <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks for your attention

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    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 problem (2003 SP3)

    Actually, that was dense of me - for a find within other characters, just use <code>FIND</code>:
    <code>=SUMPRODUCT(--ISNUMBER(FIND("H",B5:AQ5)))</code>
    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    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 problem (2003 SP3)

    Sorry, I should have been clearer - your complete formula would be:
    <code>=SUMPRODUCT(--EXACT($B$5:$AQ$5,"H"))+SUMPRODUCT(--EXACT($B$5:$AQ$5,"h"))*0.5</code>
    so that it counts 'H' and half of the 'h's
    the <code>--</code> is there to force conversion of <code>TRUE/FALSE</code> values to <code>1/0</code> respectively.
    The UDF is generic, so you would have used:
    <code>=CountIfExact($B$5:$AQ$5,"h")*0.5+CountIfExa ct($B$5:$AQ$5,"H")</code>
    similar to the SUMPRODUCT formula. It sounds as though you don't need that though if you only have one letter per day.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIf problem (2003 SP3)

    Thanks Rory

    I will go and play with that

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIf problem (2003 SP3)

    I've been trying to figure out a similar Countif. However, I don't always have cells with unique values. For example, I want to count all A's in a cell. It could be ABC, BA, AC, or A. I never have more than 3 letters in a cell and the A can only appear once. I was going to use a lot of cells to come up with a series of formulas, but if you have a nice, elegant UDF (or other solution) I'd appreciate the help!

  8. #8
    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 problem (2003 SP3)

    You can use:
    <code>=COUNTIF(B5:AQ5,"*A*")</code>
    adjusting the ranges as necessary, if you don't need it to be case sensitive?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CountIf problem (2003 SP3)

    You can use a formula like this:
    <code>
    =COUNTIF(A1:A100,"*A*")
    </code>
    The asterisks * are wildcards that stand for "any number of characters (including none)"

  10. #10
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CountIf problem (2003 SP3)

    Thanks Hans and Steve. I was making it much too hard!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CountIf problem (2003 SP3)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> The reply was from Rory, not Steve!

Posting Permissions

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