I am trying to sum a certain number of columns, based on the criteria in a
certain cell.

If my criteria is an "8", I want to sum the last 8 columns of an array. If
I change the 8 to a 9, I want the sum to automatically pick up the last 9
columns,if I change it to 10, I want the sum of the last 10 columns, etc.

Can anyone help????

try this idea where a11 has 1,2,3,4,5,etc

=SUM(B12:OFFSET(B12,1,A11-1))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Flanno" wrote in message
...
I am trying to sum a certain number of columns, based on the criteria in a
certain cell.

If my criteria is an "8", I want to sum the last 8 columns of an array.
If
I change the 8 to a 9, I want the sum to automatically pick up the last 9
columns,if I change it to 10, I want the sum of the last 10 columns, etc.

Can anyone help????

Flanno

Try using the OFFSET function with a variable for the 3rd (vertical
array) or fourth (horizontal array) aruguments.

If you have a horizontal array starting in cell C1 and your variable
(the number to be summed) is in A1 and is is n, and you put in B1 the
following formula in B1

=SUM (OFFSET(C1,0,0,1,a1))

it will sum the FIRST n values of elements in your array.

Since you want to sum the last n values, you will need to build some
logic into one the second or third arguments of the OFFSET function.
For example, if your array is 25 items long, starting in C1 and your
variable is in A1 and your formula is in B1, then you would need
something like

=SUM(OFFSET(C1,0,25-a1,1,a1)

If your array is vertical you would need to have the variables in the
"row" arguments of the OFFSET function.

Good luck.

Ken
Norfolk, Va





On May 16, 3:33*pm, Flanno wrote:
I am trying to sum a certain number of columns, based on the criteria in a
certain cell. *

If my criteria is an "8", I want to sum the last 8 columns of an array. *If
I change the 8 to a 9, I want the sum to automatically pick up the last 9
columns,if I change it to 10, I want the sum of the last 10 columns, etc.

Can anyone help????

If the range contains blanks try this array entered with CTRL+Shift+Enter

=SUM(A100:INDEX(A1:A100,LARGE(ROW(A1:A100)*(A1:A10 0<""),B1)))

where B1 contains the rows to sum

Mike

"Flanno" wrote:

I am trying to sum a certain number of columns, based on the criteria in a
certain cell.

If my criteria is an "8", I want to sum the last 8 columns of an array. If
I change the 8 to a 9, I want the sum to automatically pick up the last 9
columns,if I change it to 10, I want the sum of the last 10 columns, etc.

Can anyone help????