Published on
LibreOffice Calc can answer the same business question as Microsoft Excel: what is the total of a team's open amounts? Start the comparison with data and expected results, rather than assuming that similar-looking files always behave identically.
| Team | Amount in EUR | Status |
|---|---|---|
| North | 120 | Open |
| South | 80 | Open |
| North | 30 | Closed |
| North | 50 | Open |
With headers in A1:C1 and records in A2:C5, the expected open North total is 170 EUR. These examples use English function names and semicolon separators; account for the actual application settings when entering them.
=SUMIFS(B2:B5;A2:A5;"North";C2:C5;"Open")The vendor documents SUMIFS from LibreOffice 4.0. All criteria must be met together. Matching range sizes and genuine numeric amounts are part of checking the calculation. LibreOffice on SUMIFS.
The help identifies LET as available from LibreOffice 24.8. It can name the result within a formula:
=LET(total;SUMIFS(B2:B5;A2:A5;"North";C2:C5;"Open");total)The independently calculated result remains 170 EUR. An older release need not support the same functions as a current installation. The documentation also identifies LET as outside the OpenFormula standard in OpenDocument 1.3. Function availability and exchange format are separate checks. LibreOffice on LET.
Calc describes named database tables with column references such as Records[Amount]. Define a vertical database range and suitable column headings. This is more than naming one cell. The help explicitly warns that saving in OpenDocument Spreadsheet (ODS) converts structured references to direct references. A correct current result therefore does not establish that later table growth will work as intended. LibreOffice database table references.
Save in the agreed format, close the file and reopen it in the recipient's application. Append an open North record worth 20 EUR and expect 190 EUR. This checks both the present result and the promised extension behaviour. The guide was reviewed against vendor documentation and independently calculated expected values; it does not claim a native test in every historical release. Compare Google Sheets, Apple Numbers and PlanMaker in the other parts.
