Excel Array Formula returning #REF! when using INDIRECT with dynamic ranges in Office 365
I'm sure I'm missing something obvious here, but I'm migrating some code and I'm working on a personal project and I'm working with an scenario where an array formula involving `INDIRECT` is returning a `#REF!` behavior in Excel Office 365..... My intention is to dynamically reference a range based on the values in two cells. For instance, I have the starting and ending rows specified in cells A1 and A2, respectively. I am trying to sum a range of values in column B from the specified rows using the following formula: ```excel =SUM(INDIRECT("B" & A1 & ":B" & A2)) ``` However, when I change the values in A1 or A2 to reference a non-existent row (for example, A1=5 and A2=2), the formula throws a `#REF!` behavior. I expected it to return 0 or ignore the non-existing rows instead. I've tried wrapping the `INDIRECT` function with `IFERROR`: ```excel =IFERROR(SUM(INDIRECT("B" & A1 & ":B" & A2)), 0) ``` This still results in a `#REF!` behavior rather than returning 0. I suspect that the question lies in how `INDIRECT` handles dynamic ranges, especially when they point to invalid references. I've also checked the contents of A1 and A2 to ensure they are numeric and within valid range limits. Any advice on how to properly handle this scenario while maintaining the ability to dynamically adjust the row references would be greatly appreciated. I'm using Excel for Microsoft 365 (16.0.14527.20226). Any ideas what could be causing this? I'm using Excel 3.10 in this project. The stack includes Excel and several other technologies. I'd really appreciate any guidance on this.