Reputation: 348

gSheets: How to use SPLIT in ARRAYFORMULA over columns

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.

Upvotes: 2

Answers (4)

player0

Reputation: 1

use:

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

Upvotes: 0

Dang D. Khanh

Reputation: 1471

use:

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

F3= (replace ISEVEN -> ISODD)

Upvotes: 1

Marios

Reputation: 27390

Try this:

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

Upvotes: 1

basic

Reputation: 11968

Another option:

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

Upvotes: 1

gSheets: How to use SPLIT in ARRAYFORMULA over columns

Answers (4)

Related Questions