How to count strikethrough text?

[_dave]

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’.

sample.ods

[Lupp]

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

[LeroyG]

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.

---

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

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).

[igorlius]

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.

[_dave]

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”

sample.ods

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

[Zizi64]

Please do not use the checkmark at your own answer - if the answer (the solution) came from others really.

[Lupp]

@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 direct 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.
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).