Sigma or Summation function in Excel

Question

Given a value (in a cell), calculate a formula like this:

Ʃ(3i+1) for i from 0 to the value specified in the cell.

SUM(), SERIESSUM() are not suitable in this case. How can I do this in Excel? Much thanks!

Answer 1

To leave you all options, just use this:

=SUMPRODUCT(3*(ROW(A1:INDEX(A:A,B1-A1+1))-(1-A1))+1)

A1 is the lower limit and B1 is the upper limit... will also work for ranges like i = -5 to -3 ;)

Answer 2

Demo as below graph:

Write in B2 Cell with =SUM(3*(ROW(INDIRECT(CONCATENATE("1:",A2+1)))-1)+1) then press Ctrl + Shift + Enter.

Answer 3

Use the array formula:

=SUM(3*ROW(INDIRECT("1:" & A1))+1)+1

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.