I have a Google Sheet, where I have a lot of raw book-keeping data with "accounts" as an identifier.
Now I want to make "sum rows" in another sheet, where I keep a list of accounts in a cell as a comma-separated value. What I want is for this comma-separated value to be looped for all the "raw data" and then have the amount from the bookkeeping of ALL entries for these accounts summed.
Example:
RAW DATA:
| A | B |
|---|---|
| 1000 | 1.25 |
| 1000 | 1.75 |
| 1000 | 100.22 |
| 2422 | 29.00 |
| 2400 | 20.00 |
Sum sheet:
| A | B |
|---|---|
| 1000,2400 | 123.22 |
| 2422 | 29.00 |
| 2400,2422 | 49.00 |
I have tried with the following formula, but it doesnt seem to sum all of the accounts – only the first one in each comma-separated list.
=ArrayFormula(SUMPRODUCT(SUMIFS(Accounts!F:F;Accounts!A:A;TRIM(MID(SUBSTITUTE(A2;",";REPT(" ";9999));(ROW($BB$1:INDEX($BB:$BB;LEN(A2)-LEN(SUBSTITUTE(A2;",";""))+1))-1)*9999+1;9999))))))
>Solution :
For example:
Formula in E1:
=SUMPRODUCT((A:A=SPLIT(D1,","))*(B:B))