# Sum of Numbers

#### Peter100

##### Well-known Member
Hi

Is there a function to calculate the total of the figures between 1 and a given number ?

ie.
the sum of 1 - 24 = 300
the sum of 1 - 36 = 666

In reality I want to enter 24 in cell A1 and in B1 display the result 300

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Good evening Peter100

I don't believe there is, but there's a cheeky little formula that statisticians use to work it out :

((n*(n+1))/2

Where n is the number you want to sum up to:

the sum of 1 - 24 = 300
24*25=600
600/2=300

HTH

DominicB

EDIT : The formula you would want is :
=(A1*(A1+1))/2

=SUMPRODUCT(ROW(INDIRECT("1:"&A1)))

=(A1*(A1+1))/2

is far superior.

little story for illustration

about 100 years ago, when Albert Einstein was 10-12 years old or less ...

Teacher (who wants to have some free-time to prepare the next lesson): Guys & gals, take a little sheet of paper, write down all numbers from 1 to 100 and calculate the sum
Little Albert, crossing his arms and looking to the sky for 5 seconds, takes his pencil and writes down the result. Ready!
1 + 100 = 101
2 + 99 = 101
3 + 98 = 101
...
50 + 51 = 101

hmm... 50 * 101 = 5050

=SUMPRODUCT(ROW(INDIRECT("1:"&A1)))
I get #REF
this works for me
=SUMPRODUCT(ROW(1:24))
using INDIRECT in SUMPRODUCT, is it possible ?
do I miss something ?

kind regards,
Erik

Cheers All yep they both work

now I can finish my calculator

=SUMPRODUCT(ROW(INDIRECT("1:"&A1)))
I get #REF

It works for me. Of course, it's inferior to (N*(N+1))/2. Also bounded by 65536...

this works for me
=SUMPRODUCT(ROW(1:24))

Not robust: Insert a row before the formula cell to see...

using INDIRECT in SUMPRODUCT, is it possible ?
do I miss something ?
...

Sure, it's possible.

Cheers All yep they both work

now I can finish my calculator

Don't opt for the SumProduct version though...

found the solution
it was related to an error on my sheet

as a side note, this story is actually about Karl Gauss, and I believe it was Grade 2. besides, we all know that Einstein stunk at grade school math

cheers. ben.

little story for illustration

about 100 years ago, when Albert Einstein was 10-12 years old or less ...

Teacher (who wants to have some free-time to prepare the next lesson): Guys & gals, take a little sheet of paper, write down all numbers from 1 to 100 and calculate the sum
Little Albert, crossing his arms and looking to the sky for 5 seconds, takes his pencil and writes down the result. Ready!
1 + 100 = 101
2 + 99 = 101
3 + 98 = 101
...
50 + 51 = 101

hmm... 50 * 101 = 5050

Replies
7
Views
211
Replies
5
Views
136
Replies
2
Views
305
Replies
4
Views
183
Replies
0
Views
121

1,203,242
Messages
6,054,350
Members
444,718
Latest member
r0nster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back