Summing to and from depending upon input values

[mccamli]

I've tried to search for an answer to this but don't quite know how to phrase it...

I have a number of columns (1-31...looks suspiciously like a month) and want to sum a range of those columns depending upon two input cells.

So, to sum the 10th to the 18th columns I would like to be able to input the numbers 10 and 18 to drive the result.

I would prefer not to use VBA.

Any ideas? I'm sure it's easy???

Thanks

 

[Cindy Ellis]

It may not be difficult, but please clarify whether you want all of the rows of the identified columns included in a single sum for the specified columns, or a separate sum for each row?

Cindy

 

[mccamli]

It's a separate sum for each row of data...

 

[Cindy Ellis]

Making a few minor assumptions (for instance that your data starts in column A, and ends in AE, that your first row is a header, and you have entered the starting column in AG2 and the ending column in AH2), the following formula copied down to all of your data rows should work. You'll need to adjust the formula if my assumptions don't match your data.

=SUM(INDEX(A2:AE2,AG2):INDEX(A2:AE2,AH2))

Hope this helps,
Cindy

 

[mccamli]

Awesome. I use INDEX in INDEX MATCH but have been using it for so long that I forgot exactly what it did!

Thanks Cindy

Ian

 

[mikerickson]

=SUM(OFFSET($A$1, 0, (lowColumn-1), 65536, highColumn-(lowColumn-1)))

 

[mccamli]

Thanks mikerickson,

I'm summing one row at a time...have modified your offset formula to read

=SUM(OFFSET(A5, 0, ($AF$1-1), 1, $AF$2-($AF$1-1)))

Not exactly sure what it does but it also works a treat!

Cheers

Ian