excel formulas sum over array

Question

I have no clue why summing over the array formula doesn't return correct results. The formula is:

=SUM(INDEX($C$4:$C$12,TRANSPOSE(OFFSET(K17,0,0,1,COUNT(K17:S17)))))

What I want to do is select multiple values from $C$4:$C$12 based on values in K17:S17 and then sum it. In row K17:S17 there might 1 to 9 positive integers (from 1 to 9), that determine which row to select from C4:C12.

When I use just INDEX($C$4:$C$12,TRANSPOSE(OFFSET(K17,0,0,1,COUNT(K17:S17)))) as an array function it returns the values I want to be sumed properly. but adding sum in frot returns just the first value from this set.

Why not {=SUM(N(OFFSET($C$4,K17:S17-1,)))}? See: stackoverflow.com/questions/38342495/… — Axel Richter
– Axel Richter, Commented Sep 2, 2016 at 8:46

XOR LX · Accepted Answer · 2016-09-02 08:56:09Z

0

In order to pass an array as INDEX's row_num or col_num parameter, a little coercion is required:

https://excelxor.com/2014/09/05/index-returning-an-array-of-values/

Also, it's better to avoid volatile OFFSET constructions if possible.

=SUM(INDEX($C$4:$C$12,N(IF({1},K17:INDEX(K17:S17,MATCH(9.9E+307,K17:S17))))))

which, incidentally, does not require CSE.

Edit: if the maximum value in the range K17:S17 is 9, we can replace 9.9E+307 with 10:

=SUM(INDEX($C$4:$C$12,N(IF({1},K17:INDEX(K17:S17,MATCH(10,K17:S17))))))

Regards

edited Sep 2, 2016 at 8:56

answered Sep 2, 2016 at 8:49

XOR LX

7,7821 gold badge19 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel formulas sum over array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related