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
  • 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. – Jim K Mar 11 '18 at 4:28
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
  • 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. – Tylla Mar 12 '18 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.