Sum of Numbers

Peter100

Well-known Member
Joined
Apr 21, 2002
Messages
765
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
 
Upvote 0
=SUMPRODUCT(ROW(INDIRECT("1:"&A1)))

Addendum: Exploiting the mathematical regularity

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

is far superior.
 
Upvote 0
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
 
Upvote 0
=SUMPRODUCT(ROW(INDIRECT("1:"&A1)))
Aladin,
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top