Excel - using COUNTIF to find a value in a string?

[sbroomfield]

I have a table which has single values ('A', 'B', 'C', etc)
in each cell. To summarise each row I have a formula like:
=COUNTIF(C7:AF7,"P&quot. This gives me all the occurrences of P in that row.
I have now had to change the table so that it includes more than one value in a cell ('AFH', 'BC', 'PLG').
How can I still get all the occurrences of P? I've trawled through Excel Help but I can't seem to find a way of checking to see if a letter occurs in a string!

Many Thanks.

 

[DarkSun]

Insert this function into a module on the workbook then you can use the function of CountK

Syntax =COUNTIFK(Range,Value)

Example :

=COUNTIFK(C7:AF7,"P&quot This would do what you want.

Public Function COUNTIFK(MyRange As Range, MyVal As Variant)
Dim Counter As Integer
Counter = 0

For Each c In MyRange

If InStr(1, c, MyVal) <> 0 Then
Counter = Counter + 1
End If
Next

COUNTIFK = Counter
End Function

 

[DarkSun]

This is case sensitive let me know if you want me to change it.

 

[sbroomfield]

Thanks - I'll try that.

 

[sbroomfield]

I've tried that and I get a #NAME? error.
Any ideas?

 

[DarkSun]

That means it can't find the function.

Did you place it in a module on the workbook you are trying to use the Function?

If you go to Insert-Function there should be a option in the Function category called &quot;User defined&quot; with the new function. If it doesn't appear you have place the function in the wrong place.

Let me know if you can't find it.

 

[JVFriederick]

DarkSun : In case sbroomfield can't get the function stuff to work, I think the following formula will count the occurances - so long as there is only ONE instance of &quot;P&quot; in each cell (which is probably OK in this case).

{=COUNT(FIND(&quot;P&quot;,C7:AF7))}

Note : This function needs to be entered with Ctrl+Enter

Your macro code is a much better way to handle all the occurances of a string. I will definately keep it around for my use! BTW, I learn more from looking at your code than anywhere else - you really know your stuff.

 

[DarkSun]

Thanks JVFriederick, I was trying to find the Excel way to do it but got bored looking.

 

[sbroomfield]

Thanks for all your help, guys.
The VBA code worked in the end.

 

[dneufarth]

DarkSun,

Isn't Dim c As Object required?

I needed to add it to get the CountIfK function to work in Office 97.


Dave

 

[DarkSun]

It's not required, it's wise to put it in. I'm using 97 as well.