Description: My purpose was to conditionally sum values from a few rows. I have written formula (G3 cell in attached file) : =SUMIF(B2:B10,"=2",A2:A10) - this calculates good =SUMIF(B5:B10,"=2",A5:A10) - this also is good but when from some reasons I had to provide different start row index in Ranges or in SumRange then result was wrong =SUMIF(B5:B10,"=2",A2:A10) - this fails =SUMIF(B2:B10,"=2",A5:A10) - and this fails also I was testing it on debian linux system with version libre office 6.0 and latest 6.1.2 Steps to Reproduce: 1.Open attached file 2.See columns F3 and H3, should have the same values as G3 and I3 but have not. Actual Results: formula =SUMIF(B2:B10,"=2",A2:A10) returns different value than =SUMIF(B5:B10,"=2",A2:A10) Expected Results: =SUMIF(B5:B10,"=2",A2:A10) should return 3 also. Reproducible: Always User Profile Reset: Yes Additional Info: I don't see any option to attach file with example of bug
Created attachment 145248 [details] ods file with example of bug related with sumif
Created attachment 145254 [details] File sample, sumif Both ranges should have the same length in rows or columns, or the second range has only the first cell (not compatible with other file formats). But if not, only the rows on the second range with the same relative position than in range of criteria, are summed. Take a see to your file sample modified, so what rows are summed it's more visible. IMO there is not a bug, it does what you have formulated. BTW SUMIFS it's I think more friendly, because the summed range it's the first and it's easy to add more criteria at the end of the function. Please if you are not agree, reopen it.
(In reply to m.a.riosv from comment #2) > Created attachment 145254 [details] > File sample, sumif > > Both ranges should have the same length in rows or columns, or the second > range has only the first cell (not compatible with other file formats). > But if not, only the rows on the second range with the same relative > position than in range of criteria, are summed. > > Take a see to your file sample modified, so what rows are summed it's more > visible. > > IMO there is not a bug, it does what you have formulated. > > BTW SUMIFS it's I think more friendly, because the summed range it's the > first and it's easy to add more criteria at the end of the function. > > Please if you are not agree, reopen it. I see now. Thanks for clarification. I agree now that there is no bug, but would be great to mention about that use case in documentation of sumif formula.
Hi Olivier what do you think about?
The Help page of SUMIF is too amateur-ish, and even wrong. It deserves a much more precise definition, more/better examples, and corner case situations. Re-qualifying the bug as doc bug
Created attachment 175196 [details] SUMIF with different range sizes > Both ranges should have the same length in rows or columns, or the second > range has only the first cell (not compatible with other file formats). I ran some tests and this is no longer the case. SUMIF accepts ranges of different sizes. Maybe this was an enhancement after this bug was filed, but the fact is that now SUMIF does work if both ranges have different sizes (see attached example). It seems that the size of the first range always prevails, be it smaller or larger than the sum range. I guess this is due to MS compatibility: https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b In Excel, "the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range". So far, this behavior is reproducible in LO Calc as well.
Dione Maddern committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/e531b6ff5c696b0424cc63f348111a0897dedad8 #tdf120179 Rewrite SUMIF Help Page