Formula for removing lowest 3 values and summing remainder

[Jerry Atrix]

I need a formula to take a row of numbers and return the sum of them all except the lowest 3 values.

For instance:

If my row was these numbers - 5, 9, 3, 10, 7, 22, 1

The lowest 3 values are 1, 3, and 5. Take those out and it leaves 7, 9, 10, and 22.

Those added up equal 48 which is the end number I am looking for.

Any ideas?

[sktneer]

Assuming your numbers are in the range A2:G2, then try this......

Please Login or Register  to view this content.

Is this what you are trying to achieve?

[shg]

=sum(a1:a7, -small(a1:a7, {1,2,3}))

[Glenn Kennedy]

Assuming that your data are in A1 to A6... use this

Please Login or Register  to view this content.

Both answers are the same... but they do work with mixed positive/negative numbers, I think...

[Jerry Atrix]

Thanks all, that SumIf was exactly the ticket! I knew it was going to be something like that but i couldnt figure out the 'small' function inside of it.

Thanks again!

[shg]

Just a note: using sktneer's formula, if the numbers are 10, 4, 4, 4, 4, 4, 4, the result will be 10, not 22.

[sktneer]

Just a note: using sktneer's formula, if the numbers are 10, 4, 4, 4, 4, 4, 4, the result will be 10, not 22.

Correct. I think OP didn't say that there may be repeated numbers like these. But if this is the case, using yours and Glen's formula will do the trick.