Home > Software engineering >  ArrayFormula to sum amount grouped by split string
ArrayFormula to sum amount grouped by split string

Time:07-25

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: enter image description here

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


Functions used:

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

  • Related