count if

M

macamarr

I am counting text. Not having a problem getting a count but what I need to
do is; if the given words font has a strikthrough format I do not want to
include it in the count.
 
B

Bob Phillips

Then you need a UDF to identify that the text has a strikethrough and put
this in a helper column, and count in that column, There is no function to
get it directly.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

Jim May

In your Worksheet code module paste in:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Font.Strikethrough = True Then
Target.Font.Strikethrough = False
Else: Target.Font.Strikethrough = True
End If
Target.Calculate
Cancel = True
End Sub

In a Standard module paste in:

Function CountAnyOpen(myRange)
Application.Volatile
For Each i In myRange
If i.Cells.Font.Strikethrough = False Then
mytotal = 1 + mytotal
End If
Next i
CountAnyOpen = mytotal
End Function

then on your sheet (in an empty cell enter =CountAnyOpen(A1:A10) <Change
accordingly - and format

Double-click away (between A1 and A10)
HTH
 
Top