» Summing Values In a Range Specified By Indirect Cell References

If you enter this formula in cell B4 and then copy it downward into as many cells you think you will need, it will give you correct answers. Perhaps less elegant than using the OFFSET function, but I think it's more direct and more comprehensible.

=IF(ISBLANK(A4),"",RANK(A4,A:A))

My previous posted comments should have gone to this topic:

Ranking numbers in a dynamic list

» Summing Values In a Range Specified By Indirect Cell References



CATEGORY: Summing VERSIONS: All Microsoft Excel Versions



Problem:

Column A contains a list of numbers.
Columns B:C contain pairs of cell references, representing the beginning and end of a specific range from column A, which we would like to sum.
How could we create a formula that will sum the values included in each of the ranges specified in columns B:C?

Solution:

Using the INDIRECT function, as follows:
=SUM(INDIRECT(B2&"":""&C2)) ... should be

=SUM(INDIRECT(B2&":"&C2))