Bug 40110 - CountIf, SUM, SUMSQ function (and others?) do not count last non-empty cell in external file range with preceding empty cell
Summary: CountIf, SUM, SUMSQ function (and others?) do not count last non-empty cell i...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.2 release
Hardware: Other All
: medium normal
Assignee: Kohei Yoshida
Whiteboard: target:3.5 target:3.4.5
: 40652 41504 (view as bug list)
Depends on:
Reported: 2011-08-15 11:41 UTC by Midiar
Modified: 2011-12-23 17:05 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:

Sample files data.ods and formula.ods (11.50 KB, application/zip)
2011-08-15 11:41 UTC, Midiar

Note You need to log in before you can comment on or make changes to this bug.
Description Midiar 2011-08-15 11:41:05 UTC
Created attachment 50243 [details]
Sample files data.ods and formula.ods

The CountIf function will miss the last non-empty cell in the data range, if 2 conditions are met:

1. The data range must be in an external file.
2. The data range must be "irregular", in that for example the first cell in the data range is empty.

I have enclosed 2 sample files data.ods and formula.ods. Formula.ods contains 4 columns with CountIf functions.

The J2 cell gets a 0, but it should be 1, like the rest. Its data range is in data.ods, in D1:D65536 - and D1 in that file is empty, confusing the CountIf function.
Comment 1 Midiar 2011-08-15 11:45:08 UTC
This may be related to bug 34242.
Comment 2 Midiar 2011-08-16 23:15:54 UTC
Also tried the files in 3.4.2 on Win7, with the same result.
Comment 3 Markus Mohrhard 2011-10-05 22:06:01 UTC
*** Bug 40652 has been marked as a duplicate of this bug. ***
Comment 4 Rainer Bielefeld Retired 2011-10-05 22:59:17 UTC
OS and Status due to Bug 40652
I did some further tests, it seems that it's a general "calculate external range" problem.

can you proceed? Please feel free to reassign (or reset Assignee to default) if it’s not your area. Please set Status to ASSIGNED if you accept this Bug.
Comment 5 Andreas Säger 2011-10-06 04:10:25 UTC
*** Bug 41504 has been marked as a duplicate of this bug. ***
Comment 6 Markus Mohrhard 2011-10-06 04:11:56 UTC
I'll try to find some time after Paris.
Comment 7 Kohei Yoshida 2011-11-01 08:22:31 UTC
I'm taking a look at this.
Comment 8 Kohei Yoshida 2011-11-01 08:43:46 UTC
Ah, I see what's wrong indeed.  Luckily this is a trivial fix.
Comment 10 Kohei Yoshida 2011-11-01 09:29:31 UTC
The fix has been backported to the 3-4 branch.  The fix should appear in 3.4.5.