gSheets: How to use SPLIT in ARRAYFORMULA over columns

user_3763734 0
Viewed 100

For numbers x and y, I have cell data formatted as x@y.

An example row:

| A      |    B   |  C    |  D     |
| ------ | ------ | ----- | ------ |
|10@100  | 10@120 | 8@150 | 5@175  |

I want to parse this type of row into two quantities: the sum of the x's and sum of y's.

With my example, I should have two cells: 33 and 545

Basically, I want to SUM the resulting array of SPLIT applied to each cell in A1:D1.

My attempt

=SUM(ARRAYFORMULA(SPLIT(A1:D1, "@")))

Unfortunately, this approach doesn't allow me to specify whether I want x or y (when I call SPLIT) and it seems to be returning x + y, rather than sum(i=1 to 4) x_i.

google-sheets-formula google-sheets
4 Answers

Another option:

=ArrayFormula({SUM(INDEX(SPLIT(TRANSPOSE(A1:D1),"@"),0,1)),SUM(INDEX(SPLIT(TRANSPOSE(A1:D1),"@"),0,2))})

enter image description here

user_121888610

Try this:

=index(query(arrayformula(split(transpose(A1:D1), "@")),"select sum(Col1),sum(Col2) ",0),2)

result

user_112252910

use:

=SUMPRODUCT(SPLIT(JOIN("@",A1:D1),"@"),ISEVEN(SEQUENCE(1,COUNTA(A1:D1)*2)-1))

enter image description here

F3= (replace ISEVEN -> ISODD)
user_126917010

use:

=ARRAYFORMULA(QUERY(QUERY(SPLIT(TRANSPOSE(A1:D1); "@"); 
 "sum(Col1),sum(Col2)"); "offset 1"; 0))
user_56326290
Related