Sum arithmetic progression cells ?

[hpbaxxter]

• Hi!
  My question is : Is it possible to create a formula that would give the sum of cells that are in arithmetic progression in excel?
  
  Example:
  Let's first choose 4 cells that are in arithmetic progression, B14 , B20 , B26 and B32 for instance(the common difference here is 6). So what I want to do is: I want to type a formula in another cell, lets suppose C5, that will automatically give me the sum of the values of B14,B20,B26 and B32. I am aware that I can just type on C5 =B14+B20+B26+B32 but and if I wanted the sum of 90 cells??Wouldn't it be too much work to type all the cells? Does Anyone know a formula for it?
  
  thanks! :]​
  
  
  
  

 

[VBA Geek]

=sumproduct(b14:b32*(mod(row(b14:b32)-row(b14),6)=0))

 

[shg]

If you have n terms of an arithmetic series whose first value is a1 and the constant difference is d, then the sum is

=n*a + n*(n-1)*d/2

 

[hpbaxxter]

it didn't work
I think you dint understand me !
I want to sum a specific cells which they are making an arithmetic progression (unlimited cells)
as :b14,b20, b26, b32 .......

 

[shg]

You don't need all of the cells, you only need the first cell, the difference, and the number of cells:

	A​	B​
1​	14​	A1: Input
2​	20​	A2: =A1+6
3​	26​	A3: =A2+6
4​	32​	A4: =A3+6
5​	38​	A5: =A4+6
6​	44​	A6: =A5+6
7​	50​	A7: =A6+6
8​	56​	A8: =A7+6
9​
10​	280​	A10: =SUM(A1:A8)
11​	280​	A11: =8*A1 + 8*(8-1)*6/2

EDIT: Perhaps I misunderstood; if you want to sum every Nth cell, then use VBA Geek's formula.

 

[hpbaxxter]

	A	B
1	1
2	8	=A1+A3+A5+A7+A9 ..
3	3
4	5
5	0
6	9
7	7

<tbody>
</tbody>

Like in this table , I want to sum A1+A3+A5+A7+......... Can I write a function to do this calculate ?

 

[VBA Geek]

The 6 in my formula will determine how many rows to skip

=sumproduct(b14:b32*(mod(row(b14:b32)-row(b14),[B]6[/B])=0))

so in the above case each 6 rows are summed up, in your last case you need to replace 6 with 2 and also update the B14:B32 reference to A1:A17

 

[shg]

If you just want to specify the three values,

	A​	B​	C​	D​	E​
1​	1​		Beg​	2​
2​	2​		Step​	3​
3​	3​		Qty​	4​
4​	4​			26​	D4: =SUMPRODUCT(INDEX(A:A, Beg):INDEX(A:A, Beg + Step * (Qty - 1)), --(MOD(ROW(INDEX(A:A, Beg):INDEX(A:A, Beg + Step * (Qty - 1))) - Beg, Step) = 0))
5​	5​
6​	6​
7​	7​
8​	8​
9​	9​
10​	10​
11​	11​
12​	12​

 

[hpbaxxter]

The 6 in my formula will determine how many rows to skip

=sumproduct(b14:b32*(mod(row(b14:b32)-row(b14),[B]6[/B])=0))

so in the above case each 6 rows are summed up, in your last case you need to replace 6 with 2 and also update the B14:B32 reference to A1:A17

this one is a solution of my question, I didn't understand I but thank you
Can you explain it please !

 

[VBA Geek]

for more info about MOD function:

https://www.youtube.com/watch?v=btiuexHSvHY
https://www.youtube.com/watch?v=i-ExchUb_Ow
https://www.youtube.com/watch?v=AIQbJAPMrEg
MOD function - Excel

Boolean Logic:

https://www.youtube.com/watch?v=o4jU7K591Fo
https://www.youtube.com/watch?v=CPrkWG5FNrI
https://www.youtube.com/watch?v=6FJO3mPI7Yg&list=PL711948E9C292480B

Sumproduct function

https://www.youtube.com/watch?v=Q2_njKJ4PcE
https://www.youtube.com/watch?v=vxpeEf8MYaY
https://www.youtube.com/watch?v=RGwPqTG2BnM

after watching these, you can understand the formula very easily