# Count that ignores strikethrough font.

#### Tarvalon

##### Board Regular
Hello,

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

Thanks

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
A count of what? Just cells that don't have strikethrough in the range or what?

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.

There isn't a way to count based on formatting

Of course there is, it just takes VBA.

Try this UDF:

Code:
``````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
ABCDE
1****2
21****
32****
44****
55****
Sheet1

Of course there is, it just takes VBA.

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

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

Populated cells not including cells with strikethrough.

Of course there is, it just takes VBA.

Try this UDF:

Code:
``````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>

 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!!!

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)

Worked!! Thanks!

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?

Replies
34
Views
1K
Replies
1
Views
168
Replies
1
Views
59
Replies
3
Views
173
Replies
1
Views
458

1,207,255
Messages
6,077,314
Members
446,278
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back