Formula to sum amount grouped by split string

Question

Hello I have following table:

I need to count total amount for each person for t1 rows. For t1 "Comments" will always have same format. Each person delimiter will be ", " and for their % it will be " - ". I'm planning to make some wizard for this cell to ease entering persons, but not sure yet, anyway it is out of scope for this question.

So, my result table will look like:

If ArrayFormula is too complicated, then normal formula is also fine, it won't be too much rows for persons.

So far I'm able to filter rows and split persons to get range like:

Person1 - 50% | John - 50% |               | $100.00
Smith - 10%   | John - 10% | Person1 - 80% | $1,000.00
Smith - 100%  |            |               | $2,000.00

With formula:

={FILTER(ARRAYFORMULA(SPLIT(C2:C6, ", ", false)), A2:A6 = "t1"), FILTER(ARRAYFORMULA(B2:B6), A2:A6 = "t1")}

But stuck to proceed further

Live example: https://docs.google.com/spreadsheets/d/1e3IJaiOSlkia6ce9UGgIL_sNR6FK5TOp-BNrSYwS8Lg/edit?usp=sharing

Answer 1

Please use the following in cell G2 and pull down for the rest of them

=SUM(QUERY({$A$2:$B$6,INDEX(IFNA(SPLIT(REGEXEXTRACT($C$2:$C$6,E2&" - \d+"),"-")))},
        "select Col4*Col2/100 where Col3 is not null "))

(You can reformat the results to your likings using the top menu: Format > Number )

---

Functions used:

• QUERY
• INDEX
• IFNA
• REGEXEXTRACT
• SUM
• SPLIT