Sum of remainder or smallest number/s

[FRANKPM]

Hi I was wondering if any one could help me. I want to count the smallest numbers in a range of 10 cells after omitting the 4 largest. I have been using the formula =SUM(IF(ISNUMBER(LARGE(E5:N5,{1,2,3,4})),LARGE(E5:N5,{1,2,3,4})),D5), (Thanks to Robert Mikka). This works perfectly in that in the desired cell it enters the 4 largest numbers and a specified cell. I then want to be able to total the remainder. Can anyone help

 

[FRANKPM]

Is it possible for you to forget all of the suggested formulas and just to post per record the outcome you want to see?

Aladin

It is definitely possible to do that and I have a cell that flags when I need to omit a number. I was just trying to follow this to the end. As I said earlier, out of three hundred entries I have only noticed one incident and although there could be more on a different day it is still unlikely to be a lot.

Thanks for all you help.
Frank

 

[FRANKPM]

Robert
Your formula does the same as Aladins, in that it also will not omit the 5th largest number if it is the same as the 4th largest.

Again thanks for your help on this and the earlier formula.

Frank

 

[Aladin Akyurek]

Aladin

It is definitely possible to do that and I have a cell that flags when I need to omit a number. I was just trying to follow this to the end. As I said earlier, out of three hundred entries I have only noticed one incident and although there could be more on a different day it is still unlikely to be a lot.

Thanks for all you help.
Frank

Are you after summing all values except 4 largest plus the one you flag as the value to omit?

 

[FRANKPM]

Are you after summing all values except 4 largest plus the one you flag as the value to omit?

I was looking to sum all of the cells from cell 2 to 11 minus the four largest. It would return a zero if there were only four or less cells(CORRECT ANS). I would sum all cells in excess of the 4 largest(CORRECT ANS). However if any cell was equal to any of the 4 largest it would get the answer wrong. So if I had 10 9 8 7 7 5 5 5 it would give an answer of 15, when it should give an answer of 22. If I had 10 9 7 7 7 7 7 5, it would give an answer of 5 instead of 26.

But as you suggested yesterday I can manually fill in the cell in those instances.
Thanks
Frank

 

[Aladin Akyurek]

I was looking to sum all of the cells from cell 2 to 11 minus the four largest. It would return a zero if there were only four or less cells(CORRECT ANS). I would sum all cells in excess of the 4 largest(CORRECT ANS). However if any cell was equal to any of the 4 largest it would get the answer wrong. So if I had 10 9 8 7 7 5 5 5 it would give an answer of 15, when it should give an answer of 22. If I had 10 9 7 7 7 7 7 5, it would give an answer of 5 instead of 26.

But as you suggested yesterday I can manually fill in the cell in those instances.
Thanks
Frank

									Result
10	9	8	7	7	5	5	5		22
10	9	7	7	7	7	7	5		26

<colgroup><col style="width: 48pt;" span="10" width="64"> <tbody>
</tbody>

J2, copied down:

=IF(COUNT(A2:H2)>4,SUM(A2:H2)-SUM(LARGE(A2:H2,{1,2,3,4})),0)

 

[FRANKPM]

That seems to work perfectly.

Thanks AGAIN
Frank

									Result
10	9	8	7	7	5	5	5		22
10	9	7	7	7	7	7	5		26

<tbody>
</tbody>

J2, copied down:

=IF(COUNT(A2:H2)>4,SUM(A2:H2)-SUM(LARGE(A2:H2,{1,2,3,4})),0)

 

[Aladin Akyurek]

That seems to work perfectly.

Thanks AGAIN
Frank

You are welcome. Thanks for providing feedback.