Sum variable range

[SimonHughes]

Hello, I would like to sum values in column A depending if I want o sum the top 10 or 20 etc. There would be an input cell, C2 where you could enter 10 and then the first 10 values in column A would be added, changing the input cell to 15 would add the top 15 values and so on. Any help appreciated. Data would start in cell A2. I am using Excel 2010

 

[Daveydoodles]

By top do you mean the highest values or those first in the list?

 

[Brian from Maui]

Try,

=SUM(A2:INDEX(A2:A100,1,C1))

 

[jamcall]

This should work for you:
=SUM(INDIRECT("A2:A" & C2 + 1))

HTH,
~ Jim

 

[SimonHughes]

Hi All, I meant the highest values Davey. The formula from Brian is not working (may be a cell reference thing??) but the one from Jamcall is. Many thanks for taking the time to reply and help out appreciated.

 

[jamcall]

Hi All, I meant the highest values Davey. The formula from Brian is not working (may be a cell reference thing??) but the one from Jamcall is. Many thanks for taking the time to reply and help out appreciated.

Glad it helped... That works if your data is sorted (hi to lo). If you just wanted highest values without sorting (as I think Davey was alluding to) you could use something like:

{=SUM(LARGE(A:A,ROW(INDIRECT("A1:A"&C2))))}

- array formula, enter with Ctrl-Shift-Enter

You could adjust A:A to fit the range of your data as needed.

Cheers,
~ Jim