SUM of comma separated numbers for each unique value

Question

I'm trying to return the SUM of a set of numbers, which are comma seperated in another cell.

I'm listing out the unique values in column 1, and trying to SUM the numbers in column 2.

Client A 0,56.3,0,450,90,22.6,22.6,0,180,0,90,67.6,67.6,67.6,90,225,450

The expected outcome is 1879.3 for Client A.

But how can I do this for every new client and set of numbers added? I have tried a combination of SUM and SPLIT, but I can't get it work for every value in column A in an arrayformula.

Here is the file: https://docs.google.com/spreadsheets/d/1k8o_Ft59R44yQKOSY99AuAk8HDGTcRyiw2N9umONv3U/edit?usp=sharing

Thanks!

Answer 1

You could try:

=INDEX(QUERY(SPLIT(FLATTEN(A1:A&"|"&SPLIT(B1:B,",")),"|"),"Select Col1, Sum(Col2) where Col1 is not null and Col2 is not null group by Col1 label Col1 'Client', Sum(Col2) 'Total'"))

I've put the formula in F1 in your spreadsheet: