sum the largest 5 numbers on a row

[wilsoh]

Hi all,


I have been trying to sum the largest 5 numbers that are in a row

the number are in the columns stating c1, e1, g1,i1,k1,m1,o1 ect for about and other 15-20 columns, so there is a missing, so a lot of numbers

thing to note i am missing a cell between the cells i need to count/ sum

I have used the large function, but I an struggling even the excel expert at the work is stumped

can any one help

thanks for looking


Wils

[Fotis1991]

Is this useful?

http://www.excelforum.com/excel-form...le-values.html

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[wilsoh]

Hi,

uploaded file hope tho helps

i am working out percentages from the best overall score in a different class but am only using the top 5 percentages summed to find the winner


thanks

wils

[FDibbins]

Hi and welcome to the forum

1st, you can shorten the formulas in column U to this, copied down...
=SUMIF($B$3:$R$3,"%",$B4:R4)
This will let you extend your range and easier by just inserting a column before (the currect) R

Then, for your main question, use this ARRAY formula, copues down...
=SUMIFS(B4:Q4,$B$3:$Q$3,"%",B4:Q4,">="&LARGE(IF(B4:Q4<1,B4:Q4,0),5))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Tony Valko]

Something like this...

Data Range

	C	D	E	F	G	H	I	J	K	L	M	N	O
1	80	55	33	11	44	62	8	62	92	92	20	28	42
2
3
4	291

This formula entered in C4:

=SUM(LARGE((C1,E1,G1,I1,K1,M1,O1),{1,2,3,4,5}))

[wilsoh]

Hi,

thanks to all who replied,FDibbins i will set up a sheet tonight and try your answer.

Tony Valko, you answer worked and was easy for me to put in place and understand many thanks for your help.


thanks again

wils


w

[samba_ravi]

see the attachement

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.