Performance is slow after entering a cell--how to diagnose?

[zf]

Performance is slow after entering a cell on my spreadsheet, how can I diagnose? Some info:

• Entering data into any cell and then leaving it (validating it) will result in a 1-2 second delay.

• It wasn’t slow before–I was playing around with formatting (including conditional formatting).

• I copied all the cells and paste them into a new sheet. Removed conditional and direct formatting with all cells, same issue. However, as soon as I scroll the new spreadsheet down one notch, there are no delays to any cells–everything’s normal. Even if I do the same and scroll down one notch on the original spreadsheet, there are still delays and it doesn’t help.

What could be causing the performance issue? I don’t have complicated conditional formats and I would think removing all formatting of cells on the new copy of the test spreadsheet would remove all issues.

[mikekaganski]

You don’t mention your LO version and your OS. But likely you would need to also provide the data itself; and better file a performance bug.

[zf]

@mikekaganski I just found the culprit: when a cell has the value ="Person 1 count: "&SUMPRODUCT(A:A="Person 1",G:G="P",ISNUMBER(P:P),P:P>EDATE(TODAY(),-12)), it causes a delay after editing any cell and leaving it (including cells not involved in the calculation). I have 3 cells doing the same calculation for different people and when deleting these cells, performance is restored. I only have 70 rows worth of data.

Would you expect these calculations to result in performance drop? Otherwise, I might submit a bug but it’s sensitive data and I will need to sanitize it when I get the chance. I’m on Arch Linux LibreOffice 6.4.2.

[zf]

@mikekaganski Update: I am now using the COUNTIFS counterpart and that solved the issue for me–no real “lag” is noticed. I came across this page which did some benchmarks on formulas that do summing of multiple criteria and based on using COUNTIFS for LibreOffice, I assume LibreOffice uses very similar if not the same implementation for these functions. Still, I did not expect such a drastic performance difference where SUMPRODUCT struggled with only 70 rows and 15 columns of data. SUMPRODUCT is very flexible but at the cost of performance at least in some situations.

However, I’m not sure how to use ISNUMBER(P:P) test in COUNTIFS's version of what I want: ="Person 1 count: "&COUNTIFS(A:A,"Person 1",G:G,"P",P:P,">"&EDATE(TODAY(),-12)).