Getting Error 538 appears inconsistently

English
, ,
#1

I using the sum product formular which looks like this:
SUMPRODUCT(($Table1.$C$8:$AD$100007=A1)*$Table1.$B$8:$B$100007)

I used this yesterday and everything was fine. I kept using ths formular on some more columns because I needed to check other values in these areas aswell.
But than “Error:538” appeared. Also results from yesterday turned into the error. I had also the case that some values of a column were calculated, while others had an error, which is very confusing.

I found that topic (see below) and it says there might be to much values for the area and that is also what the toolbar says. But the area did not change.
I also used that formular with a smaller area in other tables, but I also got the “Error:538” SUMPRODUCT(($Table3.$C$8:$O$10886=A1)*$Table3.$B$8:$B$10886)

So the size of the area doesnt really seem to be the issue. At least not directly.

Libreoffice Calc mode.sngl - err:538 - English - Ask LibreOffice

Any ideas why the error appears

Edit: There was on ‘)’ to much at the end. It is correct in the file. I just mixed up copying and manual writing.

Edit2: I screwd up twice since I used “Named Ranges” but did not want to use the name I used in the formular. So names were different, but the table is the same, just diffrent columns.

Goal: Each line of the table contains multiple IDs (one per column) and column B has a value how often they appear in that combination. Each ID can only be 1 time per line.
On another table I have a list of IDs. The result should sum how often an ID appearce over all combinations.

#2

Welcome @lebenamdatenlimit!
Sorry, but could you explain in simple terms what exactly you’re trying to calculate? Do you want to compare each value in the rectangular matrix with the value in cell A1? Or do you want to compare each cell in the matrix with its corresponding cell—C8 with A1, C9 with A2, E15 back with C8? And after you get 1 (true) in several cells in one row, you want to multiply each of them by the corresponding value in column B on another sheet and sum the results? And which sheet is cell A1 on? I don’t know what’s wrong with your formula.

#3

Each line of the table contains multiple IDs (one per column) and column B has a value how often they appear in that combination. Each ID can only be 1 time per line.
On another table I have a list of IDs. The result should sum how often an ID appearce over all combinations.
So A1 is compared to all values in the column as well as A2 until A* shall be compared to the whole matrix. The second part is correct.
A1 is just in a third table.

The formular works. The result, if shown, is correct. I dont get why the error does appears in some cells and and dont in others. Also why it worked one day but not the next.

I havent tried to use the source cell, where all IDs of one line are in one cell. So I might try to use this column with another formular. That should be possible, I think.
Still I wonder why I have this inconsistent behaviour of LibreOffice.

#4
  • Both your formulas are incorrect because parentheses aren’t paired: One closing parenthese to much.
  • Both your formulas are misusing the SUMPRODUCT() function.
  • A correction by replacing the multiplying operater with a semicolon would cause a mismatch in range sizes.

I’m afraid you won’t get a reasonable answer as long as you don’t attach an example .ods showing the issue and tell percisely what you want to achieve.

#5

I just recognised I messed up by transfering the formular. It is correct in the file since I get results (I also edited the original post). The issue is not the formular itself, but that I get the error sometimes and sometimes I dont get it, while using the same formular, with just ‘A1’ is changing to A2, A3…An.

#6

That’s fact.

1 Like
#7

So maybe you meant not A1 but $A1?

#8

Basically yes. Since I only put the formular down I did not set the ‘$’.

#9

Since the formular is basically working and the error is inconsistent, the upload might not even help.

Let me visualise the issue at least with a screenshot. As you can see I do get results in most of the cells. The results are also correct. But the column G shows several errors in line 20, 22, 32, 34.

The formular is the same for the whole column, except the referrence to “E” always points to the cell in the same line.
=SUMPRODUCT(($Table1.$C$8:$AD$100007=E20)*$Table1.$B$8:$B$100007)

2026-01-23_LibreOfficeCalc_error
2026-01-23_LibreOfficeCalc_error303×720 18.6 KB

This is one part of the file. I have multiple areas like this with different set of IDs but the build is equal. I have the feeling that the error starts appearing when the formular is used more often in that file. Which would be wierd.

Column F does play no role.

#10

Or it might help, as others may “see” more than you…
Your decision…
.
One point “we” check usually, is if the shown data is really a number or if that is perhaps a rounded value etc.

#11

Hey, Michael, if SUMPRODUCT() isn’t working as you expect, maybe you could try calculating the same thing using other, simpler functions? Something like =COUNTIF($Table1.$C$8:$AD$100007;$A1)*something else?

1 Like
#12

I know what you mean.
On the other hand: The formular worked when I created that. Without changing anything at the formular or the database the error appears.
I was just working on an other part of the file/table.
So more or less just a recalculation made the result disappear and the error appear. It cant be some formating issue.

I will think about building a anonymised version of it.

#13

…but this could be an error in the data, an error in the addressing of cell ranges, an error in the user profile…

#14

While building the file for the upload I think, I found the reason for the error. While building it the program crashed several times.
It seems to be a performance issue. Not sure if it is the enviroment or the portable version of LibreOffice.

Anyway I opened the file on an other PC with the installed version and the file worked totally fine. No issues at all.
The errorcode was missleading.

Sometimes it can be so “easy”.
Thanks for the your effort.

#15

If you have still problems on the first PC you could try safe-mode. If this loads ok, Backup and reset the profile. Problems in profile sometimes produce problems in unexpected places…

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.