Excel – Limitations to the Excel INDIRECT() Function

microsoft excelworksheet-function

If I have numerical data in A1 and A2 then:

=SUM(A1:A2)

works and:

=SUM(INDIRECT("A1:A2"))

works and:

=SUM(A1,A2)

works but:

=SUM(INDIRECT("A1,A2"))

does not work. I am guessing that this is because "A1,A2" is not a valid cell reference.

Can anyone explain why this is so, or point me to some online documentation that gives the explanation ??

Best Answer

  • The SUM function takes a list of numbers or references as arguments. When you separate ranges with a comma, you are sending two arguments (references) to the SUM function.

    The INDIRECT function accepts one ref_text argument (and the optional a1 argument) and "returns the reference specified by a text string."

    SUM function, Syntax:

    SUM(number1,[number2],...)

    number1 (Required)

    The first number you want to add. The number can be like 4, a cell reference like B6, or a cell range like B2:B8.

    number2-255 (Optional)

    This is the second number you want to add. You can specify up to 255 numbers in this way.

  • Related Question