CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
AlexZd
AlexZd

Reputation: 2152

Formula to sum amount grouped by split string

Hello I have following table:

enter image description here

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:

enter image description here

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

Upvotes: 0

Views: 266

Answers (2)

April Zeng
April Zeng

Reputation: 9

enter image description here

Here is the formula below:

=SUMPRODUCT(B:B,ARRAYFORMULA(IFNA(REGEXREPLACE(REGEXEXTRACT(C:C,E2&" - [0-9]+%"),E2&" - ",""),)))

① REGEXEXTRACT(C:C,E2&" - [0-9]+%") - Extract "person - percentage" like text with the specified person name. For example, person name is Person1 and the formula result is Person1 - 50%.

② REGEXREPLACE(①,E2&" - ","")) - Extract the percentage only from the result of ①. In the example above, the formula result is 50%.

③ ARRAYFORMULA(IFNA(②,)) - Generate an array of percentages corresponding to the person (Person1).

④ SUMPRODUCT(C:C,③) - Sumproduct the amount and percentages corresponding to the person (Person1)

Upvotes: 1

marikamitsos
marikamitsos

Reputation: 10573

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 "))

enter image description here

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

Functions used:

Upvotes: 3

Related Questions