Sum of remainder or smallest number/s

FRANKPM

New Member
Joined
Jul 16, 2012
Messages
26
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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

New Member
Joined
Jul 16, 2012
Messages
26

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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:

4718 91277

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

FRANKPM

New Member
Joined
Jul 16, 2012
Messages
26

ADVERTISEMENT

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

Thanks Frank
 

FRANKPM

New Member
Joined
Jul 16, 2012
Messages
26
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
 

FRANKPM

New Member
Joined
Jul 16, 2012
Messages
26
Hi Aladin.

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

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top