Cell identifier number

问题:

I need to use a SUM function over an Excel range, for example, =SUM(A12:A25).

Is there a way to store the numbers 12 and 25 in another two cells and use them in the SUM function?

回答1:

Yes, For example you might put 12 in B1 and 25 in B2 and apply:

=SUM(INDIRECT("A"&B1&":A"&B2))  

INDIRECT:

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

回答2:

Instead of INDIRECT, you should use INDEX because it is not volatile. If B1 shows the first row and B2 the last one then your formula would be:

=SUM(INDEX(A:A,B1):INDEX(A:A,B2))

If B1 is 5 and B2 is 7, then it will sum B5:B7 (and as said, in a not volatile way). In over 90% of the cases INDEX() and INDEX():INDEX() will be a better solution than OFFSET and/or INDIRECT. ;)