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

[eli-damon]

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?

[PYS]

Hi

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

See Array functions

Regards

[eli-damon]

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.