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!
>Solution :
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:
