Hey everyone! I’m having trouble with a formula after moving my spreadsheet from Excel to Google Sheets. In Excel, I used this formula to sum a range:
=SUM(B10:INDEX(B10:AJ10,$D$5-1))
It worked fine there, but now in Google Sheets, it’s not doing what I want. The INDEX part is giving me a value instead of a reference like it did before. This messes up my whole calculation.
Does anyone know a way to make this work in Google Sheets? I really need to keep the same functionality. Maybe there’s a different function or method I could use? Any help would be awesome! Thanks in advance!
hey there! i’ve run into this too. try using OFFSET instead of INDEX. it works great in sheets:
=SUM(B10:OFFSET(B10,0,$D$5-2))
this should do what u want. OFFSET is awesome for making dynamic ranges in sheets. lemme know if u need more help!
I’ve encountered this issue before when migrating from Excel to Google Sheets. The INDEX function in Google Sheets does indeed behave differently. Here’s a workaround that should maintain the functionality you’re looking for:
Instead of using INDEX, try using the INDIRECT function combined with ADDRESS. Here’s how you can modify your formula:
=SUM(B10:INDIRECT(ADDRESS(ROW(B10),COLUMN(B10)+$D$5-2,4)))
This formula constructs a reference dynamically based on the value in D5, similar to what your original Excel formula was doing. The ‘4’ in the ADDRESS function ensures it creates a relative reference.
Just be aware that INDIRECT can be computationally intensive for large datasets, so if you’re working with a massive spreadsheet, you might notice some performance impact. In most cases though, this shouldn’t be an issue and should solve your problem nicely.
I’ve dealt with similar Excel-to-Sheets conversion hiccups before. Here’s a Google Sheets-friendly alternative that should work for you:
=SUM(B10:OFFSET(B10,0,$D$5-2))
The OFFSET function creates a reference range starting from B10 and extending to the column specified by D5. This approach mimics your original formula’s behavior without relying on INDEX.
One advantage of using OFFSET is that it’s generally more efficient than INDIRECT for larger datasets. It’s also more intuitive if you need to modify the formula later.
Remember to double-check your results after implementing this change. If you run into any issues or need further clarification, don’t hesitate to ask!