Getting Error 538 appears inconsistently

[lebenamdatenlimit]

I using the sum product formular which looks like this:
SUMPRODUCT(($Table1.$C$8:$AD$100007=A1)*$Table2.$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)*$Table4.$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

[JohnSUN]

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.

[lebenamdatenlimit]

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.