Ask Your Question

How to count strikethrough text?

asked 2020-12-30 09:59:26 +0100

_dave gravatar image

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


edit retag flag offensive close merge delete



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

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

3 Answers

Sort by » oldest newest most voted

answered 2021-01-01 05:59:02 +0100

_dave gravatar image

updated 2021-01-01 06:04:58 +0100

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"


Jobs           12      “=COUNT(A3:A999)”
Closed          5      “=COUNTIF(C3:C999, "=Closed")”
All open job    7      “=(COUNT(A3:A999) - COUNTIF(C3:C999, "=Closed"))”
edit flag offensive delete link more



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

Zizi64 gravatar imageZizi64 ( 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.
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).

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

answered 2020-12-30 15:51:20 +0100

LeroyG gravatar image

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 (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 (Answer markCorrect answer mark) to the left of the answer that solves your question.

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

edit flag offensive delete link more

answered 2020-12-30 14:23:39 +0100

igorlius gravatar image

updated 2020-12-30 19:21:30 +0100


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

  • 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
End Function

Result: image description

Hope it helps.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-12-30 09:59:26 +0100

Seen: 70 times

Last updated: Jan 01