0

I have a bigger spreadsheet (a few hundred rows, ~ 30 columns), and I would like to display a cell in the header with the number of errors (number of cells that have errors in them) in this spreadsheet (or in a range).

Rationale: sometimes I make bigger changes in the layout and on these occasions some formulas go south and currently there is no other way for me to test for such errors than to manually run over the spreadsheet to see if I find any errors.

I have searched for this in different ways, there are some solutions for Excel (like using an array function =SUM(IF(ISERROR(A1:C10),1))) but these do not work in LO. As I understand the biggest problem is that ISERROR() in LO does not work for ranges.

Is there a compact solution for this problem?

Edit: the LibreOffice version in question is 4.3.3.2

Improve this question
1
  • As mentioned in my answer, the formula you gave works for me. Perhaps you could give a simplified example that shows when the problem occurs, and also mention the version of LibreOffice. Commented Mar 11, 2018 at 4:28

1 Answer 1

Reset to default
1

SUMPRODUCT is generally more powerful than other similar functions.

=SUMPRODUCT(ISERROR(A1:C10))

As a bonus, this does not need to be entered as an array formula, even though it works like one.

Also, the formula you gave works for me when entered as an array formula. Tested on LO 5.4.3.2.

=SUM(IF(ISERROR(A1:C10);1;0))

Or simply:

=SUM(ISERROR(A1:C10))
Improve this answer
1
  • Hmmm, I must have missed something, because when I tried the formula that I wrote, it didn't worked, or worked really strangely - producing some strange results. And now I'm unable to reproduce the bad behavior. Anyway your first solution is perfect, that was what I was looking for. Commented Mar 12, 2018 at 14:28

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.