# Thread: CountIf problem (2003 SP3)

1. ## 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

Cheers

Steve

2. ## 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>

3. ## 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>

Cheers

Steve

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

5. ## 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.

6. ## Re: CountIf problem (2003 SP3)

Thanks Rory

I will go and play with that

Cheers

Steve

7. ## 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. ## 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?

9. ## 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. ## Re: CountIf problem (2003 SP3)

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

11. ## 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
•