Calc DSUM function ignores records beyond row 473

English
#1

Calc DSUM ignores records after line 473, while filter function counts all records of same data

=DSUM(ARCHIV22.External_Query,“MILES”,A1:B1)

Simple Sum of “MILES” field for transactions from Jan thru July.
Criteria cell A1, Date
Criteria cell B1, >=44562 …from formula =">="&Date(2022,1,1)
Sheet range “External_Query” confirmed defined to Row 1000, Column P.

No format changes in Sheet “Link to external data” for nearly 500 records so far.
CSV records past line 473 are ignored by DSUM function, while filter functions count all records.

Is DSUM buggered in LibreOffice Calc ?

8/3/22 No joy with re-format / remove TEXT fields. See DSUM Test.ods

DSUM Test.ods (65.3 KB)

#2

The typical suspect is dates acually imported as text, wich often escapes people, even as you state “no format changes”
.
If you’d like us to check, please save s copy of your spreadsheet, remove all other columns or their contents and upload the resulting file.
.
Otherwise: Text in columns is usually signaled by a leading ’
For Dates you can also put the “format” temporarily to Integer/Number. A text will resist.

#3

@ramsy, anything is possible. You have an example, you know best…
And we can only guess.

#4

Also View|Value Highlighting Ctrl+F8 is useful.

#5

Your formula:

=DSUM(ARCHIV22.External_Query;"MILES";A1:A2)

The ARCHIV22.External_Query range is defined as follows (Ctrl+F3):

$ARCHIV22.$A$3:$O$473

Sum rows 3:473 values.

2 Likes
#6

Nicely contradicts the

The general rule applies: never trust anything a user tells - always check yourself :wink:

2 Likes
#7

This kind of error is very common when importing external data.
I would add:
never trust anything a user tells or whatever you think - always verify. :slightly_smiling_face:

#8

Excellent Find sokol92. Thanks to everyone here who helped.

Perhaps LibreOffice Calc menu function bug should be reported?

  • Data / [Define or Select] Range - is deceptive. (Ctrl+F3) range(s) not shown
#9

To view Named ranges, use Menu / Sheet / Named Ranges and Expressions / Manage (Ctrl+F3). Some operating systems may use other keyboard shortcuts.
Menu / Data / [Define or Select] Range works with DataBase Ranges.
In Calc formulas, you can only refer to Named ranges.
In your case, the situation is further complicated by the fact that the same name is used.

#10

Excellent Information

Menu / Sheet / Named R&E / Manage (Ctrl-F3), is “Undocumented” in LibreOffice Help

  • Menu / Sheet / Named R&E / Define, is in Help but hides existing ranges
    .
  • Menu / Sheet / Named R&E / Insert, is in Help & shows ranges but prohibits editing

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.