@Ander I think the desired output is to sum all first elements of each array, all second elements, all third etc.
@Welley_Rezende I am pretty much sure that this is a data design problem, and the solution would be to organize the data differently, ideally in a way that each of the arrays’ elements was a value on a separate row:
Or at least imported in an already transposed way, i.e.
[1,0,1,2][0,0,10,-5][5,-5,5,10], so that it’s easier to
However, let’s say for some reason this is the only way you can have it. If the number of elements in each sub-array is the same, here’s what I’d do:
- Flatten the array with
ListCombine(). E.g. in your case this will result in a list with 12 elements.
- Get the number of elements in each of the arrays (in your case 3)
- Set up the outer
Sequence(1, Elements count) so that you have 1, 2, 3.
- Set up the inner
Sequence(CurrentValue, Total count, Elements count) so that you have a sequence starting from 1 or 2 or 3, running up to 12, but with an increment of 3 and not 1. This will give you numbers: [1, 4, 7, 10], [2, 5, 8, 11], [3, 6, 9, 12].
- Within the inner sequence’s formula map, get the
Nth(CurrentValue) element of the flattened list. This will result in a “transposed” array. You can
Sum() right away.
Easier shown than explained:
TL;DR: the inner loop will give you numbers with the step of 3, with starting number shifted by 1 each time. These are the indices that you then can use in Nth.
For the record, if you remove
.Sum(), that’s how you transpose that 2-dimensional array: