The metacharacters in the text cause the cell to be ignored when comparing its contents. This becomes a problem with the $ sign. Indirectly referencing helper cells using referenced letters solves the IFSUM problem, but it’s anything but elegant or ingenious. Is there a direct solution?
00000_LO-CALC_6 summierte Währungen_124250.ods (35.7 KB)
Or escape the $ in the search:
=SUMIF($C18:$I18;"\"&K$15;$B18:$H18)
2 Likes
There are two options:
-
You disable regular expressions (
Tools → Options → LibreOffice Calc → Calculate). -
You use the SUMPRODUCT function, because this function does not allow regular expressions anyway.
2 Likes
For this one specific file, I prefer by @mariosv solution 3.
Solution 2 is brilliant, but I need to study it because I want to sum without multiplying within matrix-areas.
Solution 1 is out of the question because regular expressions are enabled globally in my case; they can’t be disabled in individual files.
It’s important to know in depth that SUMIF, unlike SUMPRODUCT, respects regular expressions, as I haven’t found any information like that anywhere.