Dynamic sum of number of cells

[Dennis Foldager]

Hi all

I'm working on a spreadsheet where I would like to be able to sum a certain number of cells automatically. E.g. I put in 120 or 100 in my reference cell and Excel then gives me the sum of the top 120 or 100 cells in my revenue column. Is there a way to do this? I've thought about simply naming the range of cells like "top120revenue" and "top100revenue" etc. but besides being static it is also complicated when working with 27 sheets. If a formula exists it would be easier to simply change the sheet reference instead of relabeling the range of cells.

Hope someone can help!?

[Fotis1991]

Assuming that your numbers starts in A2, and reference cell is C1, then this formula should do this.

Please Login or Register  to view this content.

[benishiryo]

hi Dennis Foldager, welcome to the forum. uploading a sample Excel file will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
ideally, it should contain your desired results

but assuming data is in A2:A121 & your cell reference that contains 120 or 100 is in B1, then:
=SUM(INDIRECT("A2:A"&B1+1))
the "+1" is because my data starts from A2.

[Dennis Foldager]

Thanks for the fast replies! Really appreciate the help.

I haven't been able to make your suggestions work so I've tried to attach a small sample for you guys

Once again thanks alot



Sample.xlsx

[Fotis1991]

In F4 type how many rows you want to count

In F5, put this formula.

=SUM(F8:INDEX(F8:F5000,F4))

Do you have your expected result?

[Dennis Foldager]

Yes...perfect thanks! Will mark as solved now!