Google-sheets – How to sum a column with cells that have multiple values separated by a comma

I'll describe the solution step by step:

Get all names in separate cells

=split(join(", ", filter(A2:A, len(A2:A))), ", ", False)

does this, by taking nonempty cells in the range A2:A (this is what filter is for), joining them using the same comma-space separator you have within cells, and then splitting by that separator (not by space or comma separately).

Transpose

This is easy: just add transpose.

=transpose(split(join(", ", filter(A2:A, len(A2:A))), ", ", False))

Get unique names only

This is only if you don't want the counts. unique does the job then.

=unique(transpose(split(join(", ", filter(A2:A, len(A2:A))), ", ", False)))

Get unique names and their counts

For this I would use query instead of unique:

=query(transpose(split(join(", ", filter(A2:A, len(A2:A))), ", ", False)), 
       "select Col1, count(Col1) group by Col1", 0)

Which says: group by name, and include the count of repetitions. The last argument "0" says the data has no header row, which it doesn't since we started with A2.

You want to display the relevant year that matches a Day/Month value in Column B that is understood to be sorted in descending order.

The format of the Day/Month values are not consistent. Some values are dates while others are strings. This is confirmed by isdate().

There are two formulas:

Cell A1:

• =if(row()=1,2019,1)

Cell A2, copy to bottom of page:

• =if(row()=1,2019,if(index(SPLIT(to_text(B1), "/"), 0, 2)=index(SPLIT(to_text(B2), "/"), 0, 2),A1,if(index(SPLIT(to_text(B1), "/"), 0, 2)>index(SPLIT(to_text(B2), "/"), 0, 2),A1,A1-1)))

The formula in Cell A2 deserves explanation:

• if(row()=1,2019

  • if this is row 1, then the year = 2019, otherwise...

• to_text(B1)

  • converts the Day/Month value in the preceding row to a string; it doesn't matter whether the value was a date or a string, it will be converted to a string.
• index(SPLIT(to_text(B1), "/"), 0, 2)
  • `splits the string in the preceding row and returns the second value as a number value - this represents the month of the year.
• if(index(SPLIT(to_text(B1), "/"), 0, 2)=index(SPLIT(to_text(B2), "/"), 0, 2),A1
  • tests if the month value in the preceding row equals the month value of this row
  • if yes, then the year is the same as the preceding row, otherwise...
  • if no, then the following formula applies...
• if(index(SPLIT(to_text(B1), "/"), 0, 2)>index(SPLIT(to_text(B2), "/"), 0, 2),A1,A1-1
  • tests if the month value in the preceding row is greater than the month value of this row,
  • if yes, then the year is the same as the preceding row,
  • if no, then the preceding month must be less than the current month. Since the dates are sorted in descending order, this can only mean that a new year has begin, so the value of the year for this row is the preceding value minus one.

To do:

• Insert the relevant formula in Cell A1 and in Cell A2
• Copy the formula in Cell A2 to the bottom row of Column A
• Select all the cells in column A (Cell A1 to the bottom of the sheet) > Copy > Paste Values.