Ask Your Question

sum of function of cells in range - Impossible? Really?

asked 2018-09-29 13:41:22 +0200

eli-damon gravatar image

I need a formula that applies a function to the value of each cell in a range and then adds the results. I cannot use an auxiliary column because the function includes a relative reference to another cell, and it will be copied row after row for an indefinite number of rows. In other words, want to be able to write something like "=mysum(x^H2 for x in D2:D17)" in cell K2, "=mysum(x^H3 for x in D2:D17)" in cell K3, etc.

This seems like an obvious thing to me, but I have not found anything in my searches that would allow me to it. Is it really impossible to do this?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-09-29 13:50:22 +0200

pierre-yves samyn gravatar image


If I understand your need you can try something like =SUMPRODUCT($D$2:$D$17^H2)

See Array functions


edit flag offensive delete link more


It works. Thank you so much.

I was not familiar with the idea of array formulas. When I first saw your suggestion, I was like, That doesn't make any sense. I tried it, and it worked, but I didn't understand why it should work. But when I looked up the documentation on array formulas it made sense.

eli-damon gravatar imageeli-damon ( 2018-09-29 15:37:32 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-29 13:41:22 +0200

Seen: 75 times

Last updated: Sep 29 '18