Count number of times a certain character appears in a given cell, even if said character no longer exists, using a "non macro" method?

[appreciatethehelp]

OS Mojave 10.14.6, LO 7.0.6.2

The pupose of this is to make a tally of the number of times a student fails a question, without having to make a separate sheet logging all of their attempts; whether or not they have failed is shown via a character in a certain cell that shows the answers, then the answers are cleared, hence the fact that the character triggering the count no longer exists.

E.g. say cell A1 is the answer cell, and B1 is the counting cell.
If I put the right character (say “Y” for example) in A1, B1 should show “1”, even after the “Y” has been cleared in A1.
If Y appears again in A1, B1 should show 2, even once the “Y” is cleared again.
Another way to put it is I want to count the event of “Y” appearing, rather than the existence of “Y”, if that makes sense.

I suppose a macro could be used to achieve this, however am wondering whether this can be done via a formula or another way?

I ask as I still don’t know how to program macros and my experience using the macro recorder has elicited some pretty inconsistent/strange results.

[keme]

You can do this by self-referencing cells. You need to change some settings for this to work:

• Menu item Tools - Options,
• Left pane expand LibreOffice Calc - Calculate
• Tick Iterations
• Set Steps and Minimum change to 1

The typical formula structure is
=IF( <condition> ; <this value> + <desired change> ; <this value> )

Note that any event which makes recalculation necessary, may count the existence of Y once again.

See attachment, which also provides a reset option (X)