Adding 7 highest numbers

[Stu]

Hi all

This sounds like it should be easy but for the life of me I cant work it
out.

What I have is a row of numbers and I have to add the 7 highest value
numbers out of a possible 9 otherwise just add what ever is in the row

for instance
row 1 contains 94, 96, 91, 92, 95, 90, 96, 98 total should equal 662
row 2 contains 96, 97, 93, 93, 96, 91, 97 total should equal 663
row 3 contains 95, 95, 92, 94, 94, 92 total should equal 562

any help would be gratefully appreciated

Stu

 

[Guest]

=IF(COUNT(A1:H1)<7,SUM(A1:H1),SUM(LARGE(A1:H1,{1,2,3,4,5,6,7})))

 

[Dave Peterson]

One way:

=SUM(LARGE(A1:I1,ROW(INDIRECT("1:"&MIN(7,COUNT(A1:I1))))))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

or if there could be 0 entries:

=IF(COUNT(A1:I1)=0,"",
SUM(LARGE(A1:I1,ROW(INDIRECT("1:"&MIN(7,COUNT(A1:I1)))))))
(one cell and still an array formula)

 

[Guest]

If your amounts are in columns A through I, try using this:

H1:
=SUM(LARGE(I1:INDEX(A1:I1,LARGE(IF(A1:I1<>"",COLUMN(A:I)),MIN(7,COUNT(A1:I1)))),ROW(INDIRECT("1:"&MIN(7,COUNT(A1:I1))))))

Note: To commit that array formula hold down [Ctrl] and [Shift] when you
press [Enter]

Then copy that formula down.

Does that help?

••••••••••••••••
Regards,
Ron

 

[Ron Rosenfeld]

Hi all

This sounds like it should be easy but for the life of me I cant work it
out.

What I have is a row of numbers and I have to add the 7 highest value
numbers out of a possible 9 otherwise just add what ever is in the row

for instance
row 1 contains 94, 96, 91, 92, 95, 90, 96, 98 total should equal 662
row 2 contains 96, 97, 93, 93, 96, 91, 97 total should equal 663
row 3 contains 95, 95, 92, 94, 94, 92 total should equal 562

any help would be gratefully appreciated

Stu

You could use the **array** formula:

=SUM(LARGE(rng,ROW(INDIRECT("1:"&MIN(7,COUNT(A1:I1))))))

where rng is where your numbers are entered.

To enter an **array** formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.


--ron

 

[Stu]

[snip]
Thanks to all

Stu