COUNTIF not working in certain cells

[weeniebeenie37]

I have a spreadsheet that originated on Office as xlsx, it’s now saved in ODS format.

I was using COUNTIF to find certain data and it was working fine in all cells I wanted it to. I had to change the format for the wildcard as I was told it was different to Excel. Everything was fine.

Then I deleted a row in my data, the formulas automatically updated but one of the calculation columns stopped worked, reset to 0. I checked the formula, still correct. I then tried copying the formula next door, as is, and that resulted in 0.

In short, this formula in cell M12:

=COUNTIF(C157:C217, “.AUD.”)

Gave the correct result, that exact same formula in cell L12 - L16 results in 0, whereas it used to work.

EDIT: This continues to happen If I delete rows inside the formula. Even if I then undo and re-instate them.

[erAck]

My crystal ball is broken, I can’t see the document.

[LKeithJordan]

It might help we could see the worksheet in question. Would you mind attaching a copy of the workbook?

BTW, erAck, thanks for the guidance about answer vs. comment. I’m a forum newbie. All help is appreciated.

[LKeithJordan]

I believe I am correct that you should enable regular expressions (REGEX) for your formula to work properly. Go to Tools > Options > LibreOffice Calc > Calculate and check “Enable regular expressions in formulas.”

(Please click the check mark to the left of my response if you believe it is the best answer to your question.)