I have the address of the beginning and end of a range stored in especial cells like below:
I want to use SUM function on this range like this:
SUM('P&L'!C3:'P&L'!D3)
I tried the INDIRECT function but I face an error:
=SUM(INDIRECT(F2 & ":" & F3))
Function INDIRECT parameter 1 value is ''P&L'!C3:'P&L'!D3'. It is not a valid cell/range reference.
Looking at the sample data below
You could just use indirect to both ranges since both will then return cell references, and then sum those references and combine into a single range using :.
=sum(indirect(F2):indirect(F3))
These two formulas work. See this google sheet for examples.
=sum(INDIRECT("'P&L'!C3:D3",true))
=sum(INDIRECT(F2&":"&F3,true))
=sum(INDIRECT("'P&L'!"&'P&L'!F2&":"&'P&L'!F3,true))
Also, this will create a volatile formula, a better choice would be to use Index as a range.
Please try the following
In your cells use P&L!C3 and P&L!D3
Your formula would be:
=SUM(INDIRECT(A4),INDIRECT(A5))
where A4 and A5 are your cells
