calc

How to “group by” and sum or count in LibreOffice Calc (Excel)

 in Applications  tagged calc / excel / group by / LibreOffice / Subtotal by

In the world of spreadsheet applications, LibreOffice Calc stands out as a versatile and powerful tool for managing data. It offers a wide array of features that can help you organize, analyze, and make sense of your data. One of these features, often underutilized, is the Subtotals functionality. In this blog post, we’ll explore how to use the Subtotals functionality in the Data menu of LibreOffice Calc to count how many times each item is repeated in a dataset. This is particularly useful when working with large datasets or lists, as it allows you to create summary reports without the need for complex formulas or manual counting.

Preparing Your Data

Start by opening LibreOffice Calc and loading the dataset you want to analyze. Ensure that your data is organized in columns and that each item you want to count is in a separate column. For example, if you have a list of products, each product name should be in its own column.

Sorting Your Data

To use the Subtotals functionality effectively, your data needs to be sorted by the column containing the items you want to count. To sort your data:

  • Select the entire dataset by clicking and dragging your mouse.
  • Go to the “Data” menu, and then click on “Sort.”

Sort Data

  • In the “Sort Criteria” dialog box, select the column containing the items you want to count.
  • Choose the sorting order (ascending or descending), and click “OK.”

Your data is now sorted and ready for subtotal analysis.

Using the Subtotals Functionality

With your data sorted, you can now use the Subtotals functionality:

  • Select the entire dataset again.
  • Go to the “Data” menu and click on “Subtotals.”

Subtotals

In the “Subtotals” dialog box, you’ll see options for grouping and summarizing your data. By default, it may suggest using the first column for grouping, which is what you want in most cases.

Subtotals Dialog

  • In the “Function” dropdown, choose the type of summary you want, which is “Count” in this case.
  • Make sure that the “Replace current subtotals” option is selected.
  • Click “OK.”

LibreOffice Calc will now calculate the subtotal counts for each item in your dataset and insert them into your spreadsheet. It will also group items together and provide an outline to help you navigate the summary.

Subtotals Result

The Subtotals functionality creates a summary of your data by grouping items and counting them. You can expand and collapse these groups using the outlined symbols to the left of the spreadsheet. This allows you to view the summary data in a more organized manner.

The Subtotals functionality in LibreOffice Calc is a powerful tool for analyzing data and generating summary reports. Whether you’re working with product lists, customer data, or any other dataset, Subtotals can help you count how many times each item is repeated without the need for complex formulas or manual counting. By following the steps outlined in this blog post, you can harness the full potential of LibreOffice Calc and make your data analysis tasks more efficient and accurate. Give it a try, and you’ll be amazed at how Subtotals can streamline your data analysis workflow.

LibreOffice Calc: Finding duplicate / common entries between two columns

 in Office  tagged calc / common / duplicate / if / ISERROR / LibreOffice / match / matching / RANDBETWEEN by

This video demonstrates how to find all matching values between two columns in LibreOffice Calc. Precisely, in this video, we fill in columns A and B with random integer values between the numbers 1 and 100 (inclusive). The formula to generate the random values was the following:

=RANDBETWEEN(1;100)

After we filled in columns A and B with random values, we used the following formula in each cell of column C to find all common values between the first two columns:

=IF(ISERROR(MATCH(B1;A:A;0));"";B1)

LibreOffice Cal commands used in this video

  • RANDBETWEEN(Bottom; Top) – Returns an integer random number in a specified range.
  • IF(Test; ThenValue; OtherwiseValue) – Specifies a logical test to be performed.
  • ISERROR(Value) – Tests for error conditions, including the #N/A error value, and returns TRUE or FALSE.
  • MATCH(SearchCriterion; LookupArray; Type) – Returns the relative position of an item in an array that matches a specified value. The function returns the position of the value found in the lookup_array as a number.

LibreOffice Calc: Get workbook path only

 in Office  tagged calc / cell / CELL("filename") / char / find / left / len / LibreOffice / substitute by

Get workbook path only

For Linux and Mac

=LEFT(
  CELL("filename"),
  FIND(
    CHAR(1),
    SUBSTITUTE(
      CELL("filename"),
      "/",
      CHAR(1),
      LEN(CELL("filename")) - LEN(
        SUBSTITUTE(
          CELL("filename"),
          "/",
          ""
        )
      )
    )
  ) -1
)&"'"

For Windows

=LEFT(
  CELL("filename"),
  FIND(
    CHAR(1),
    SUBSTITUTE(
      CELL("filename"),
      "\",
      CHAR(1),
      LEN(CELL("filename")) - LEN(
        SUBSTITUTE(
          CELL("filename"),
          "\",
          ""
        )
      )
    )
  ) -1
)&"'"

Bonus

If you want to remove the ' characters as well that are around the filename and path use the following  solution, this will allow you to create hyperlinks for the folders as well

=REPLACE(
  LEFT(
    CELL("filename"),
    FIND(CHAR(1),
      SUBSTITUTE(
        CELL("filename"),
        "/",
        CHAR(1),
        LEN(CELL("filename")) - LEN(
          SUBSTITUTE(
            CELL("filename"),
            "/",
            ""
          )
        )
      )
    ) -1
  )
  ,1,1,""
)

To create hyperlink as well, use the following

=HYPERLINK(
  REPLACE(
    LEFT(
      CELL("filename"),
      FIND(CHAR(1),
        SUBSTITUTE(
          CELL("filename"),
          "/",
          CHAR(1),
          LEN(CELL("filename")) - LEN(
            SUBSTITUTE(
              CELL("filename"),
              "/",
              ""
            )
          )
        )
      ) -1
    )
    ,1,1,""
  )
)

 

 

LibreOffice Calc: Get workbook full path with filename

 in Office  tagged calc / cell / CELL("filename") / find / left / LibreOffice by

Get workbook full path with filename

=LEFT(CELL("filename"),FIND("#",CELL("filename"))-1)

Explanation

If you want to get the workbook filename and path, you can do so with a formula that uses the LEFT and the FIND function.

The CELL("filename") function is used to get the full file name and path along with current sheet link:

The result will look like this:

'file:///home/user/path/documentName.ods'#$sheetName

Using the FIND function we find the position of the delimiter # and then using the LEFT function, we extract just the full directory path with the filename. We know the number of characters to extract by locating the the position of the hash # character with FIND and then subtracting 1 (to exclude itself as well).

Extra

If you want to remove the ' characters as well that are around the filename and path use the following solution

=REPLACE(
  LEFT(
    CELL("filename"),
    FIND(
      "#",
      CELL("filename")
    )-2
  ), 1, 1,""
)