Search code examples
excelconcatenationlibreoffice-calc

Concatenate and sum hours depending on condition in Excel / Calc

I am having this example in Excel / Calc (download here):

enter image description here

I would like two things:

  • to sum automatically TIME of category = MANAGEMENT (the same for MEETING)
  • to concatenate (separated by ; ) description of category = MANAGEMENT (the same for MEETING). In case of MANAGEMENT it should be like this: Task 1; Task 3; Tastk 5; Task 7; Task 8.

How can I do these two options to have a matrix of two lines (Management and Meeting) and two columns (Times and Descriptions)?

Solution

  • To find the sum, you can use the SUMIF() or SUMPRODUCT() function

    =SUMIF($A$2:$A$1000;O2;$E$2:$E$1000)
=SUMPRODUCT($A$2:$A$1000=O2;$E$2:$E$1000)

    Just format the result as time and instead of 0.145833333333333 you get 03:30

    The TEXTJOIN() function will help you to combine the required strings

    {=TEXTJOIN(";";1;IF($A$2:$A$1000=O2;$B$2:$B$1000;""))}

    Do not forget to enter it as an array formula, finished entering Ctrl+Shift+Enter

    Example