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:

IND1

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:

IND2

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:

IND3

If we run it through the Evaluate Formula process, you will see how it is now converted to a proper data range:

IND4

INDIRECT converted the text string to a cell range reference as designed! Nice work, Excel.

Hope you find this useful.

Happy Excelling!

Related Post

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