# Sum of a number series

#### JimM

##### Well-known Member
Hi guys

I'm looking for a formula that will sum a sequential number series eg for 1 to 5 it would do 1 + 2 + 3 + 4 + 5 = 15

I know I've seen it on here somewhere but haven't been able to find the answer using search criteria

Anyone able to point me in the right direction

Cheers

Jim

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### Dave Patton

##### Well-known Member
D1 has 1
E1 has 5

=SUMPRODUCT(ROW(INDIRECT(D1&":"&E1)))

=E1*(E1+1)/2 I believe credit to C. F. Gauss ~ 1785

#### hardeep.kanwar

##### Well-known Member
Hi guys

I'm looking for a formula that will sum a sequential number series eg for 1 to 5 it would do 1 + 2 + 3 + 4 + 5 = 15

I know I've seen it on here somewhere but haven't been able to find the answer using search criteria

Anyone able to point me in the right direction

Cheers

Jim

I think we Need more Details

#### dms37

##### Board Regular
Assuming it's just a sequence from 1 counting up ie. 1, 2, 3,..... If the sequence ends at n (or a number in a cell), the formula is

Total = n*(n + 1)/2

So if you have in A1 the length of the sequence you will need

=A1*(A1+1)/2

#### West Man

##### Well-known Member
If the number sequence does not start with one, then the more general formula is(assuming the range is A3:A15):

=(A3+A15)*(A15-A3+1)/2

#### pgc01

##### MrExcel MVP
If the number sequence does not start with one, then the more general formula is(assuming the range is A3:A15):

=(A3+A15)*(A15-A3+1)/2

Hi West Man

If you are thinking about an arithmetic progression, I believe you mean:

=ROWS(A3:A15)*(A3+A15)/2

#### West Man

##### Well-known Member
Hi PGC,

I was not thinking as generally as you, as I was looking at sequential numbers where the start value was not one in which A15-A3+1 should be equivalent to ROWS(A3:A15), but your way is slightly cleaner and certainly handles increments other than one.

Clearly superior.

#### pgc01

##### MrExcel MVP
I was looking at sequential numbers where the start value was not one in which A15-A3+1 should be equivalent to ROWS(A3:A15), ...

You are right, if the increment is 1 the 2 formulas are equivalent.

I think, however, that Hardeep is right, we need more information. If the values were in a range we would simply use =SUM(A3:A15). I don't think that's what Jim wants.

#### West Man

##### Well-known Member
Ok, so the minumum we must know to get the sum is the starting number, the ending number and the increment - or we must have a way of computing these. Assuming these are in C2:E2 respectively, then a formula would be: =(C1+D1)*((D1-C1)/E1+1)/2

Of course if we knew the number of cells to be summed, we could get by with either the starting or ending number and the increment.

Replies
4
Views
252
Replies
6
Views
527
Replies
10
Views
509
Replies
8
Views
292
Replies
12
Views
448

### Forum statistics

1,190,897
Messages
5,983,444
Members
439,843
Latest member
PlanetFitness ### 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