Count that ignores strikethrough font.

[Tarvalon]

Hello,

I need a count that will ignore font that is strikethrough. Is that possible?

Thanks

 

[Scott Huish]

A count of what? Just cells that don't have strikethrough in the range or what?

 

[mathchick]

I'm guessing you mean you want to count how many things in a list aren't striked through? There isn't a way to count based on formatting, but if you can put an x in a separate column you could easily create a formula that will count all except those with an x.

 

[Scott Huish]

There isn't a way to count based on formatting

Of course there is, it just takes VBA.

Try this UDF:

Function CountNoStrikeThrough(r As Range) As Long
Application.Volatile
Dim c As Range, d As Long
For Each c In r
    If c <> "" Then
        If c.Font.Strikethrough = False Then d = d + 1
    End If
Next c
CountNoStrikeThrough = d
End Function

Excel Workbook
	A	B	C	D	E
1	*	*	*	*	2
2	1	*	*	*	*
3	2	*	*	*	*
4	4	*	*	*	*
5	5	*	*	*	*
Sheet1

 

[mathchick]

Of course there is, it just takes VBA.

I should have said there's no way to do it without VBA.

 

[Tarvalon]

A count of what? Just cells that don't have strikethrough in the range or what?

Populated cells not including cells with strikethrough.

 

[Tarvalon]

Of course there is, it just takes VBA.

Try this UDF:

Function CountNoStrikeThrough(r As Range) As Long
Application.Volatile
Dim c As Range, d As Long
For Each c In r
    If c <> "" Then
        If c.Font.Strikethrough = False Then d = d + 1
    End If
Next c
CountNoStrikeThrough = d
End Function

Sheet1

*	A	B	C	D	E
1	*	*	*	*	2
2	1	*	*	*	*
3	2	*	*	*	*
4	4	*	*	*	*
5	5	*	*	*	*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula E1 =CountNoStrikeThrough(A1:A20) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Umm...what? Where do I put that script? My range is E5 through BF9.

Thanks for your help!!!

 

[Scott Huish]

Right click on one of your sheet tabs and Click View Code.
This will take you into the Visual Basic Editor
From the menu on top, click on Insert, then Module.
Paste my code into the white area.
Hit Alt-q
If you are using XL2007 or higher, save the workbook as type .xlsm, otherwise .xls

Then in your worksheet, you can use the formula:
=CountNoStrikeThrough(E5:BF9)

 

[Tarvalon]

Worked!! Thanks!

 

[becksterpas25]

Right click on one of your sheet tabs and Click View Code.
This will take you into the Visual Basic Editor
From the menu on top, click on Insert, then Module.
Paste my code into the white area.
Hit Alt-q
If you are using XL2007 or higher, save the workbook as type .xlsm, otherwise .xls

Then in your worksheet, you can use the formula:
=CountNoStrikeThrough(E5:BF9)

Can you take this a step further and sum the values in cells that are not struck through? Or can it only be used to count number of cells with no strikethroughs?