Description: SUMIFS only uses Range1 and Criteria1, it ignores all other Ranges and Criterias Steps to Reproduce: Make this table: A B C D E 1 | a | green apple | apple | =SUMIFS(A:A,B:B,"*"&D1&"*",C:C,"*"&D1&"*") 1 | Orange | | banana | =SUMIFS(A:A,B:B,"*"&D2&"*",C:C,"*"&D2&"*") 20 | a | red apple | 1 | banana | 1 | kiwi | 1 | banana | Now E1 should equal 21. Why? because if you paste C1 into B1 then E1 equals 1, that means SUMIFS is only using range1 and criteria2 ignoring all others Expected Results: 21 Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: For each row, the value of column A should be summed if either the value of column B or C matches the word D. Regex expressions should be like any other software, "*" alone doesn't mean anything, it should have something before it, for example: .* a* [1-9]* etc
Created attachment 175472 [details] Reproduced bug
> For each row, the value of column A should be summed if *either* the value of > column B or C matches the word D. You misunderstood the purpose of the SUMIFS function. Replace the word *either* by *both*, and you have the correct definition of the function. SUMIFS sum cell that meet all criteria for each row. =SUMIFS(A:A,B:B,"*apple*",C:C,"*apple*") adds A1 if B1 *and* C1 contains "apple" A2 if B2 *and* C2 contains "apple" etc...
In addition to comment 2: Ref: relevant help page https://help.libreoffice.org/latest/en-US/text/scalc/01/func_sumifs.html?DbPAR=CALC > The logical relation between criteria can be defined as logical AND (conjunction). > In other words, if and only if all given criteria are met, a value from the > corresponding cell of the given Func_Range is taken into calculation. There you may also find something to resolve your "additional" confusion: > Regex expressions should be like any other software, "*" alone doesn't mean > anything, it should have something before it, for example: > .* > a* > [1-9]* > etc What you see are *not* "regular expressions". By default, wildcards are enabled in Options/Calc/Caclulate. They have different simplified syntax. Ref: https://help.libreoffice.org/7.3/en-US/text/scalc/guide/wildcards.html?DbPAR=CALC
Thank you very much for the quick reply! Could we maybe change this into a "feature request"? :) Something like: SumIfsOr, SumIfsAny
This link shows some ways in MS Excel that you can compose SumIfs with other functions to achieve what you are trying to do. I expect some of these to work in LO Calc as well. https://www.ablebits.com/office-addins-blog/2020/09/30/excel-sumif-multiple-criteria/