# How to count strikethrough text?

I would know if it is possible to count the number of "strikethrough" texts and how to do it. I have enclosed a sample file of what I would like to be able to do.

Thank you for all the help or recommendations'.

C:\fakepath\sample.ods

edit retag close merge delete

1

Isn't this just a COUNTCOLOR() or COUNTIF(...;color) in disguise?
The one valid response to all the questions of this kind -though not an answer, but an advice- was and is: Don't code information you want to evaluate later by setting attributes!!!!
Do as @LeroyG suggests, and you can in addition get the strikeout by ConditionalFormatting.

(As compared with variants using background colors, strikeout -or any character attribute- is even worse, because it will be invisible as soon as the cell no longer shows a content or result, but will still be present as a cell attribute. In addition it may be unclear to unexperienced users, that the attribute will NOT be detected by means like the macro also discussed here, if only "most of the content" has it. The cell asked for oCell.CharStrikeout e.g. will not know about it then. Don't do it!!)

( 2020-12-30 15:43:08 +0100 )edit

Sort by » oldest newest most voted

First of all - a big thanks to Lupp for a great "warning", I can understand where he was coming from - THANK YOU. I took his advised and used LeroyG with a small change (saving a column) and being able to "add" other count routines as needed. I added the functions "COUNT" and "COUNTIF" to get my results. I enclosing the revised sample as well.

Thank you all and have a "Happy (Better) New Year" and please "BE SAFE"

C:\fakepath\sample.ods

Jobs           12      “=COUNT(A3:A999)”
Closed          5      “=COUNTIF(C3:C999, "=Closed")”
All open job    7      “=(COUNT(A3:A999) - COUNTIF(C3:C999, "=Closed"))”

more

1

( 2021-01-01 08:29:50 +0100 )edit

@Zizi64: You are right, of course, As far as I was the helper behind, it's no matter, however.
(@)All: The wholesome comment @_dave started his answer with, motivated me to look at the topic another time. After all we have similar question again and again, and many a questioner insn't in the least ready to accept and advice.
I would suggest they use helperfunctions to first filter their sheets based on the dircet formatting they had used , and then to rework them in a way no longer resorting to this error prone way of representing information.
The mentioned helper functions and an example of their application are contained in the attachment.
C:\fakepath\cellPropertiesFinal.ods
Unfortunately there isn't a comparably efficient way to pass ranges to user functions without relying on VBAsupport 1. Therefore the example may not run under older versions of LibO (and definitely not in AOO).

( 2021-01-01 16:48:19 +0100 )edit

Add an extra column (e.g. C) entitled Closed, and put 1 next to each job closed.

Better: Use the title Open, and put 1 next to each job open, and 0 next to each job closed. Use =COUNT(C4:C15) or =COUNT(A4:A15) to for the Total Jobs, =COUNTIF(C4:C15;"0") for the Total Closed Jobs, and =SUM(C4:C15) for the Remaining Job count.

Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.

Check the mark () to the left of the answer that solves your question.

If the answer helped you, you can mark the up arrow () that is on the left (to vote, you need to have karma of at least 5).

more

Hello,

i wrote a macro solution that does what you want. Just put it into the Tools -> Macros -> "Edit Macros" and you can call it from inside any cell via

=COUNTSTRIKES("B4:B15")

• The first argument is the range quoted as a string. You can still select the range normally but you have to add quotes ("") before submitting the formula

Here the function code:

Function COUNTSTRIKES(range as string)
range = Trim(range)
Dim count As integer
Dim i As Integer
Dim j As Integer
Set oSheet = ThisComponent.CurrentController.ActiveSheet
Set oRange = oSheet.getCellRangeByName(range)
count = 0
For i = 0 To oRange.Rows.getCount() - 1
For j = 0 To oRange.Columns.getCount() - 1
Set oCell = oRange.getCellByPosition( j, i )
if (oCell.CharStrikeout = 1) then
count = count + 1
REM print oCell.String
endif
Next
Next
COUNTSTRIKES=count
End Function


Result:

Hope it helps.

more