How can I use a cell's content for a column index?

Question

For example, in cell M1, I store the column index (A or B or C...) that I want to use later.

If now I put character D in M1, by using =SUM($M$1:$M$1), I want to get the summation of column D. And if I change D to A in M1, I want the summation of column A instead.

Any suggestion?

Answer 1

Here's an option without the INDIRECT. Unlike @pnut's, it will only work for columns A to Z (or L in this case), but since you're basing it off M1, maybe that's okay:

=SUM(INDEX(A:L,0,CODE(UPPER(M1))-64))

Answer 2

Hopefully =SUM(INDIRECT(M1&":"&M1)) will serve.