Referencing a cell in Excel using values from other cells
Use & to create the cell coordinates, access them using INDIRECT().
=SUM(INDIRECT("B1:B" & A1))
INDEX is normally preferable to INDIRECT, where possible. You don't have to "hardcode" the column, and it's not (as) volatile.
=SUM(B1:INDEX(B:B,A1))
See the documentation for more details on usage.