Bug 120179 - [LOCALHELP] rewrite SUMIF Help page
Summary: [LOCALHELP] rewrite SUMIF Help page
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
6.1.2.1 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Help-Changes-Features
  Show dependency treegraph
 
Reported: 2018-09-28 08:59 UTC by Pawel
Modified: 2021-09-22 13:21 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
ods file with example of bug related with sumif (8.96 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-09-28 09:00 UTC, Pawel
Details
File sample, sumif (11.24 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-09-28 17:20 UTC, m.a.riosv
Details
SUMIF with different range sizes (9.37 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-09-22 13:21 UTC, Rafael Lima
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Pawel 2018-09-28 08:59:46 UTC
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
Comment 1 Pawel 2018-09-28 09:00:47 UTC
Created attachment 145248 [details]
ods file with example of bug related with sumif
Comment 2 m.a.riosv 2018-09-28 17:20:46 UTC
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.
Comment 3 Pawel 2018-09-28 18:03:38 UTC
(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.
Comment 4 m.a.riosv 2018-09-28 20:48:05 UTC
Hi Olivier what do you think about?
Comment 5 Olivier Hallot 2018-09-29 05:27:03 UTC
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
Comment 6 Rafael Lima 2021-09-22 13:21:32 UTC
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.