COUNTIF but not strikeout

[jrmcfall]

First off, I love this forum, you guys are great.

With that said is there a way to use countif to evaluate a cell and then count those cells unless they have strikethrough formatting?

Example: (Imagine the bold is strikethrough....I couldn't figure out the code for that....[s][/s] didn't work.)

	A	B
1	dog	beagle
2	dog	mutt
3	cat	tabby
4	dog	lab
5	dog	mix breed
6	dog	runt

I need to count the rows with dog but not dog. If that makes sense. It should return 2.

[FDibbins]

is there a way to use countif to evaluate a cell and then count those cells unless they have strikethrough formatting?

Short answer - no, not with regular formulas. formulas work on data, formatting is cosmetic, not data.

If there is some logic behind the formatting (whatever it might be), we may be able to base the count on that?

[jrmcfall]

Basically, I have an inventory spreadsheet that has a 'cameras' worksheet with a list of cameras and their serial numbers. Based on that I have a 'material used' worksheet that has the same listing as the 'cameras' worksheet so that when you use one of the cameras you mark it with a job number and date used and it strikesthrough that camera on the 'cameras' worksheet. So now I want just a general overview 'inventory' worksheet with the amount of a specific type of camera is on hand.

I know that is confusing so I am attaching the spreadsheet so you can kind of see what I am talking about.

ASG Inventory.xlsx

[FlameRetired]

So from your last post I gather you want a simple count of what's still available that is listed "unused" in the Material Used sheet.

Try this formula in D3 of Inventory sheet.

Please Login or Register  to view this content.

[jrmcfall]

That's exactly what i needed it to do. Thanks for the assistance.

[FlameRetired]

You're welcome. Glad it helped. Thanks for the feedback and the rep!

[DoNoEvil]

I seek an answer to the original question: a COUNTIF that doesn't count Strikethroughs. I searched and searched to no avail. This thread is the closest to what I'd like

I understand it would be a VBA function. I found one to CountNoStrike, but it counts ALL cells without a strikethrough in the range, not just those I wish to COUNTIF - so it's a problem if a "cat" row is also a strikethrough

Public Function CountNoStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
If Not pRng.Font.Strikethrough Then
xOut = xOut + 1
End If
Next
CountNoStrike = xOut
End Function

It would be like: COUNTIFnoStrike( A1:A6, "dog" ) and it would return a value of 2 for the example above.

Thanks in advance!

[FDibbins]

DoNoEvil welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

(also, please use code tags when posting code - see my footnote on how to use them )