MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sums.....


Posted by Mark on March 12, 2001 4:17 AM

I have a problem where I want to sum a range from say b1 to z1. easy enough, but if a1 gives a number say 3 the sum should add up the next three columns b1 to d1, Or if a1 had 10 in then the sum should add the next ten columns....

Hopefully this makes sense, and someone out there has got the answer


Posted by Aladin Akyurek on March 12, 2001 5:27 AM

=SUM(INDIRECT("B1"&":"&ADDRESS(1,COLUMN(B1)+A1-1)))

where A1 contains the column number that marks the end of the range for SUM.

Aladin

Posted by Mark W. on March 12, 2001 7:54 AM

=SUM(OFFSET(B1,,,,A1))

Posted by Aladin Akyurek on March 12, 2001 8:53 AM

This is a good one. Congrats.