I'm trying to use this formula:

=SUMPRODUCT(VLOOKUP(B$4778,$D$4:$DC$4623,{4,5},0))

It works fine but I'd like to try to use a variable for the {4,5} portion of the formula (columns in the array to be summed) as the formula needs to change based on sheet inputs.

I have cells on the sheet that are to be used to set the initial and final columns to be searched (likely 10 columns, but the 10 columns would have to be selected from 90 some columns available).

I tried to use the Columns function but it didn't seem to work for me.

The columns are selected by users entering in cells the upper and lower limits of the search range and then I convert those values to the corresponding numerical column value.

So if they select 5 as lower and 10 as upper limit, I know I have to add 7 to get the correct data column on the data page (column 12) and likewise for upper (column 17).

The entire possible area to search is $D$4:$DC$4623. So, in the formula, if I wrote it out long ways: =SUMPRODUCT(VLOOKUP(B$4778,$D$4:$DC$4623,{12,13,14,15,16,17},0)).

I'd prefer to write it out using variables, something like this: =SUMPRODUCT(VLOOKUP(B$4778,$D$4:$DC$4623,{w:r},0)) where w would be 12 and r would be 17.

Thanks for any help!

also posted at: https://www.excelforum.com/excel-programm…tml#post4620770