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]

Thanks For your help Robert.

 

[FRANKPM]

Hi again Aladin.

I applied the earlier formula to over 300 entries and it worked but then I found a glitch. In one row my smallest number and my 4th largest number is a 7 and the formula leaves a blank as it does not choose one of the 7. Any ideas.
Thanks
Frank

 

[Aladin Akyurek]

Hi again Aladin.

I applied the earlier formula to over 300 entries and it worked but then I found a glitch. In one row my smallest number and my 4th largest number is a 7 and the formula leaves a blank as it does not choose one of the 7. Any ideas.
Thanks
Frank

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

expects 4 or more numbers, including 0's. This will include the ties of the 4th largest.

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

will ignore all of the ties of the 4th largest.

 

[FRANKPM]

Hi Aladin.
I am afraid this did not work. This is what I have

37

7

46

7

13

112

0

<tbody>
</tbody>

and I want the 0 to be replaced by the omitted 7. It works for the example below

12

43

25

28

44

8

152

8

<tbody>
</tbody>

Any ideas.

Thanks Frank.

 

[Aladin Akyurek]

Hi Aladin.
I am afraid this did not work. This is what I have

37

7

46

7

13

112

0

<tbody>
</tbody>

and I want the 0 to be replaced by the omitted 7. It works for the example below

12

43

25

28

44

8

152

8

<tbody>
</tbody>

Any ideas.

Thanks Frank.

What is the outcome you want to see for the sample you posted?

 

[FRANKPM]

What is the outcome you want to see for the sample you posted?

Hi Aladin. I would like to see a 7 in the last cell. A different cell has counted the largest 4 numbers thanks to earlier help. But i need this cell to count the sum of the numbers in excess of the 4 numbers. The 112 is the total of the 4 highest numbers

Thanks

 

[Aladin Akyurek]

Hi Aladin. I would like to see a 7 in the last cell. A different cell has counted the largest 4 numbers thanks to earlier help. But i need this cell to count the sum of the numbers in excess of the 4 numbers. The 112 is the total of the 4 highest numbers

Thanks

What is the outcome you want to see for A2:N2 below:

37

7

46

7

13

2

112

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

 

[FRANKPM]

Hi Aladin

In the cells below are 4 examples form my sheet. The formulas work for all cases except the one in row 3. The cells labeled total, sum Q1(even if it is blank or 0) and the four largest questions from column 2 to 11. The cell labeled OMIT should count or total the discarded cells. Row one is discarding the 26, 24, 12 and the 10. Row three is discarding the 20 and the 22. The second row is not working as I feel it cant discard one of the 7 as it is also the fourth largest number. It should show a 7 where the zero is.

Any ideas
Thanks
Frank

1	2	3	4	5	6	7	8	9	10	11	grade	total	#Q	OMIT
	53		28	27	27	10	12	24		26	D	135	8	72
9	37		7	46		7	13				E	112	6	0
56	32	22	30		31			20	36		C	185	7	42
38	16			36		7		16	26		D	132	6	7

<tbody>
</tbody>

 

[FRANKPM]

Hi Robert

There seemed to be a problem using your one. I tried removing spaces as you suggested. I will try again.
Thanks
Frank

 

[Aladin Akyurek]

Hi Aladin

In the cells below are 4 examples form my sheet. The formulas work for all cases except the one in row 3. The cells labeled total, sum Q1(even if it is blank or 0) and the four largest questions from column 2 to 11. The cell labeled OMIT should count or total the discarded cells. Row one is discarding the 26, 24, 12 and the 10. Row three is discarding the 20 and the 22. The second row is not working as I feel it cant discard one of the 7 as it is also the fourth largest number. It should show a 7 where the zero is.

Any ideas
Thanks
Frank

1	2	3	4	5	6	7	8	9	10	11	grade	total	#Q	OMIT
	53		28	27	27	10	12	24		26	D	135	8	72
9	37		7	46		7	13				E	112	6
56	32	22	30		31			20	36		C	185	7	42
38	16			36		7		16	26		D	132	6	7

<tbody>
</tbody>

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