After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 166769 - SUMIF(Range1,1,OFFSET(Range2,..)) "column-oriented" has wrong results
SUMIF(Range1,1,OFFSET(Range2,..)) "column-oriented" has wrong results
Status: RESOLVED FIXED
Product: Gnumeric
Classification: Applications
Component: Analytics
git master
Other All
: Normal major
: ---
Assigned To: Jody Goldberg
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2005-02-09 10:18 UTC by Helmut Wollmersdorfer
Modified: 2005-02-10 13:03 UTC
See Also:
GNOME target: ---
GNOME version: ---

Attachments
SUMIF_OFFSET combination tests sumif-test.xls (44.00 KB, application/octet-stream)
2005-02-09 10:20 UTC, Helmut Wollmersdorfer 		Details

Description Helmut Wollmersdorfer 2005-02-09 10:18:53 UTC 
Version details: 1.4.2-2 Debian/Sarge
Distribution/Version: Debian/Sarge

SUMIF(Range1,1,OFFSET(Range2,..)) calculates wrong results
  IF Range1 has width=1,heigth>1 ["column-oriented"]

See test cases in sheet SUMIF_OFFSET in the attached sumif-test.xls
The isolated example in sheet SUMIF_OFFSET_bug seems to hit the same bug.

Maybe this is related to bug 166764.

IMHO severity=major as I see no easy workaround, and it is the main reason that
I cannot use Gnumeric for professional work (= flexible financial reports) in
XL-compatible way.
Comment 1 Helmut Wollmersdorfer 2005-02-09 10:20:37 UTC 
Created attachment 37231 [details]
SUMIF_OFFSET combination tests sumif-test.xls
Comment 2 Morten Welinder 2005-02-09 16:36:42 UTC 
We seem to do the wrong thing when the first and third arguments have different
"shapes".  Do you know of any references to a detailed description of the
semantics of SUMIF?  The Excel help text isn't much help here.

Otherwise, I guess I need to figure it out from scratch.  (Sigh!)
Comment 3 Helmut Wollmersdorfer 2005-02-09 17:20:12 UTC 
The intended use is, that first and third argument have same shape (=size).

Missing specifications are a problem for testing. What XL does in case of
different shapes, is to take the smaller argument, if it is the first argument.
The first argument is fitted with the top-left corner to the top left corner of
the third argument. This logic works in Gnumeric in testcase SUMIF!D56:H56
(=line-oriented), but works not in SUMIF!D67:H67 (=column oriented).

If the third argument is smaller, it causes an error - AFAIR.

The cases in the sheet SUMIF_OFFSET use all arguments of the same "shape", i.e.
same width and heigth. I call 1 row high "line-oriented" - this works. 1 column
wide = "column-oriented" works not. Cases in SUMIF_OFFSET!C42:K42
(=line-oriented) work, SUMIF_OFFSET!C57:K57 (=column-oriented) work not.
Comment 4 Jody Goldberg 2005-02-10 13:03:44 UTC 
Fixed in head, and backported.  The patch will be in 1.5.1 and 1.4.3