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

 

[pgc01]

Hi

Assuming there are at least 4 numbers in the range, try:

=SUM(E5:N5)-SUM(LARGE(E5:N5,{1,2,3,4}))

 

[Robert Mika]

If there will be 4 less or digits in the row, or the will no 4 largest (lest say 2,2,3,4)then the remainder is ""
=IF(COUNTA(E5:N5)>4,SUM(IFERROR(IF(E5:N5>0,IF($E$5:$N$5<LARGE(E5:N5,4),$E$5:$N$5)),"")),"")
Confirm Control+Shift+Enter

 

[Robert Mika]

Just spotted that the formula did not post right:

=IF(COUNTA(E5:N5)>4,SUM(IFERROR(IF(E5:N5>0,IF($E$5:$N$5 < LARGE(E5:N5,4),$E$5:$N$5)),"")),"")

Remove spaces before and after "<".

 

[FRANKPM]

Hi pgc01.

Your formula works except when there is less than four numbers, it then enters a string of number signs. Any Suggestions.
Frank

 

[Aladin Akyurek]

Hi pgc01.

Your formula works except when there is less than four numbers, it then enters a string of number signs. Any Suggestions.
Frank

What is the desired behavior when the input consists of less than 4 numbers? If you don't want to pursue the calculation...

=IF(COUNT(E5:N5)>=4,SUM(E5:N5)-SUM(LARGE(E5:N5,{1,2,3,4})),"")

By the way, you should also test the following:

=SUMIF(E5:N5,"<"&LARGE(E5:N5,4),E5:N5)

in order to determine whether this is more appropriate, given an input like below:

4

7

1

8

9

12

7

7

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

 

[FRANKPM]

Hi Robert
The formula works if there are 6 entries but not when there are 5.

Thanks Frank

 

[FRANKPM]

Hi That will work.If there are less than 3 numbers it leaves the cell blank, for 4 numbers it displays a 0 and for more than four it totals them. Would I be asking to much to be able display a zero instead of the blank. ie when there are just 3 numbers.

Thanks again
Frank

 

[Robert Mika]

Hi Robert
The formula works if there are 6 entries but not when there are 5.

Thanks Frank

Works for me with 5 entries as well but Aladin's is the better option

 

[FRANKPM]

Hi Aladin.

The Formula =SUMIF(E5:N5,"<"&LARGE(E5:N5,4),E5:N5) works.
Thanks.
Frank.