I am trying to use the SUM function to total a (horizontal) series of cells, but make the range itself variable. The range represents a series of monthly payments and I want to be able to show the year to date total as at a month that is input / selected by the user (for example the Jan to June running total if "June" is input)
To do this the second argument in the SUM function needs to be variable. The idea is that this argument is the result of a nested vlookup function which shows the column (not the row) where the range to be summed ends.
The vlookup function by itself
=(VLOOKUP($C$3,Range,2,FALSE)&10)
produces correctly in my case “F10” (C3 is where the user inputs the month in question). I don’t want to include the row number (10) in the vlookup table hence the hypersanded addition.
The problem occurs when I enter this as the second argument in the sum function. Excel says that there is an error and won’t accept it.
Can anyone tell me what the problem is (is it the hypersand?) and how to overcome it - with or without lateral thinking? Thanks.
PS Last night my Excel program seems to have morphed into "Excel Starter". Whether or not this has got any bearing on the above I wish I could revert to what it was previously - a "non-starter" for a simpler life.
Bookmarks