Excel: A simpler way to sum a formula-based range besides INDIRECT() & ADDRESS()?
I have a master value which defines how many columns to sum. So in the below image the master value is 4, which means I am summing D4:G4 (total 1100). If I changed the value to 8, it would sum D4:K4 (total 1700).
At the moment, I'm creating a large indirect address reference. Am I over-complicating this, or is this the simplest way?
=SUM(INDIRECT(ADDRESS(ROW(D4),COLUMN(D4))&":"&ADDRESS(ROW(D4),COLUMN(D4)+$B$1-1)))
Solution
Use INDEX, like this:
=SUM(D4:INDEX(D4:W4,,B1))
- Combining tables with different column numbers
- VBA Dictionary in Custom Excel Function
- Separate octets of IP address using formulas
- Dynamic index match using indirect function to pull from other sheets
- Source data not being copied to template
- Word VBA: How to reference a Named Range/Cell from Excel so it returns the row number
- How to Combine Text Based on Multiple Criteria in Excel
- Is there a Mac/PC variant of Application.GetOpenFilename?
- How to get the path of current worksheet in VBA?
- Trying to keep last 9 of various strings
- Drag cells keeping the reference in incremental order
- Use native Excel functions to count occurrences over threshold
- The Macro takes very long time to apply on a table
- Excel Macro Compile Error - Argument not Optional
- Excel - Summing Multiple Material IDs Cost from one Cell
- Excel VBA to hide rows if more than 10 blanks
- Existing data cell not being cleared from Excel sheet
- Excel/VBA: Create a static time stamp the first time a cell is updated
- VBA - Loop through different nodes and open them
- how to append to a excel file in nodejs
- Excel VBA split single excel sheet into multiple workbooks with multiple sheets
- Microsoft Excel - How do I keep dd/mm/yyyy format while using CONCATENATE function?
- Quadratic and cubic regression in Excel
- Is it possible to insert a slicer before the vba code runs?
- Excel VBA retrieve and paste values of column A where column B equal specific value
- Mail Merge Date Swapping Month and Day
- Group of conditions in IF THEN statements in VBA
- Test if value is Long or Integer (no decimal places)
- Error 1004 when setting HPageBreak location
- Application.Match is not working correctly with dates in a VBA array