Here is another example of how we can use the INDIRECT formula to sum a dynamic range of numbers in Excel:
I had a recent request from someone who was trying to create a formula to sum a series of numbers, but wanted it to be dynamic, so that when they added more to the list, it would be included in the total. The formula they used was =SUM(“A1:A”&COUNTA(A:A)). They were trying to concatenate A1:A and the count of the items in column A (in this case 22) so it read =SUM(A1:A22), but instead it resulted in a #VALUE! error:
If we highlight within the parentheses of the SUM formula and hit F9, we’ll see that it resolves to =SUM(“A1:A22”). Note the double quotes:
That means that the data range, although it looks proper, is a text string, not a data range. To change this to a proper data range, we need to use the INDIRECT formula. INDIRECT “returns the reference specified by a text string”. So if we change the formula to =SUM(INDIRECT(“A1:A”&COUNTA(A:A))), it will now work properly:
If we run it through the Evaluate Formula process, you will see how it is now converted to a proper data range:
INDIRECT converted the text string to a cell range reference as designed! Nice work, Excel.
Hope you find this useful.