# Spreadsheet formula: lowest 100 values in a range

Is there any way I could sum up the lowest 100 values within a range?

I know that min() would give you the lowest value but i need something to return the 100 lowest values.

I just used 100 hypothetically to make it easier to understand what I'm trying to achieve.

I can use any of the following spreadsheet apps: Openoffice.org, Excel or Google Spreadsheets -- whichever works.

Thanks a lot!!!

-

Supposed that your data is in range named mydata, you have to use the following formula:

``````=SUM(SMALL(mydata,ROW(A1:A100)))
``````

entered by Ctrl+Shift+Enter.

-
I always have to be reminded about the trick of using ROW to create an array like this. Very nice. I noticed that it can be slightly simplified: =SUM(SMALL(mydata,ROW(1:3))) – Doug Glancy Apr 10 '10 at 17:17
make that: =SUM(SMALL(mydata,ROW(1:100))) – Doug Glancy Apr 11 '10 at 0:12

If an Excel macro would work then it would be easy and only take a few minutes. Record a new Macro (Tools-Macros-Record). Copy the column of values into a new column. Sort the column, in a nearby cell store the sum of the lowest 100 using a formula (=F1..F99). Copy the cell containing the needed value, then in a separate cell, do a Paste-Special and select Paste Values. This final cell contains the sum by value. Next, delete the cell containing the sum by formula, then delete the temporary column. Stop the Macro recording.

-