1

I'm looking for a function thats similar to factorial but does not build a product, but a sum.

So I have a cell with a number (in a big range) and I need to create a sum of the number and all numbers below it.

(function(12) would yield 78, function(3) would yield 6)

Several chained functions are fine as well. I'd like not to use macros or VBA as this needs to be transportable between several spreadsheet programs.

2
  • I apologize for my original answer. My ability to read is much worse than my ability to do maths. Feb 26, 2018 at 17:23
  • @Gary'sStudent No worries, you still figured the answer out faster then I did :) Feb 27, 2018 at 7:46

2 Answers 2

2

To get the sum of B9 and all the numbers below it, use:

=SUM(B9:B1048576)

If you want the sum of sequential integers below the value in A1 then use:

=A1*(A1+1)/2

This is a special case of a list of sequential integer values (not necessarily starting with 1):

Average the lowest value with the highest value. Then multiply the result by the number of values.

0
0

You can use this function combination to get the result:

=sum(row(indirect(1&":"&A1)))

where my number is written in A1. This is less elegant then Garys answer, but more flexible if you ever need to start somewhere else then '1'.

If you need to start somewhere else instead of 1 write the start number in A1, the end number in A2 and use:

=sum(row(indirect(A1&":"&A2)))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .