Another Use Of INDIRECT In Excel

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.

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

2 Responses to Another Use Of INDIRECT In Excel

  1. Modesto September 27, 2014 at 1:34 pm #

    I love yourr blog.. very nice colors & theme. Did you make this website yourself or did you
    hire someone tto doo it for you? Plz respoind as I’m looking
    to design my own blog and would like to know where u got this
    from. appreciate it

Leave a Reply