Results 1 to 5 of 5
Thread: SUM PART OF A LIST (2000)

20040223, 17:50 #1
 Join Date
 Jan 2004
 Posts
 7
 Thanks
 0
 Thanked 0 Times in 0 Posts
SUM PART OF A LIST (2000)
I need to sum the 4 highest numbers from a row of 6 numbers is this possible? i would be grateful for some help <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

20040223, 17:59 #2
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: SUM PART OF A LIST (2000)
Hi Steven
You can use the following array formula where A1:A6 represents the cells where your data is held.
=SUM(LARGE(A1:A6,ROW(INDIRECT("1:4"))))
You need to enter the formula by pressing Control+Shift+Enter and not just Enter.

20040223, 18:16 #3
 Join Date
 Jan 2004
 Posts
 7
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM PART OF A LIST (2000)
Thanx tony55 that worked perfectly could you give me an explanation of how it works? <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

20040223, 18:31 #4
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: SUM PART OF A LIST (2000)
=SUM(LARGE(A1:A6,ROW(INDIRECT("1:4"))))
To see how this array formula works you need to start in the middle and work outwards.
ROW(INDIRECT("1:4"))  In an array formula this generates a list in memory consisting of the numbers 1,2,3 and 4. The array formula then uses each of these values in turn in the rest of the formula.
The first run through will calculate LARGE(A1:A6,1) to produce the largest value, 2nd run through will get the 2nd largest value etc to produce a list in memory containing the 4 largest values from your list.
Finally the SUM part adds the 4 values to give the desired result.

20040229, 10:40 #5
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM PART OF A LIST (2000)
This type of questions is interesting, where there is always room for some ambiguity.
Let A4:F4 house:
{20,25,32,25,17,20}
Let me state up front that, in what follows, I'm not criticizing the way you worded your question... Rather, I'm trying to bring up two issues that might be of interest regarding this type of questions.
"[S]um the 4 highest numbers" from A1:F1 can be taken in two ways:
[1] Sum exactly N largest values;
[2] Sum largest N values, including the ties, if any, of the Nth value.
Let H4 house N, that is, 4.
In J4 enter:
=LARGE(A4:F4,H4)
To sum exactly N largest (case [1]), the common idiom is (as Tony has also used in his reply)...
ROW(INDIRECT(M:N))
as in:
=SUMPRODUCT(LARGE(A4:F4,ROW(INDIRECT("1:"&H4))))
Note that H4 houses N while M = 1.
The result we get wrt the sample is: 102.
If [1] is intended computation, there is a faster way that does not use volatile INDIRECT...
=SUMIF(A4:F4,">"&I4)+(H4COUNTIF(A4:F4,">"&I4))*I4
Recall that I4 houses =LARGE(A4:F4,H4).
A temporal profiling shows that this formula is twice as fast as the one with INDIRECT, even if LARGE(A4:F4,H4) is substituted for I4.
Note that [1] includes just one instance of the Nth largest value. In our sample there are two of them in A4 and in F4.
[2] wants to include all instances of the Nth largest value. This requires a different formula:
=SUMIF(A4:F4,">="&I4)
or, with the contents of I4, directly incorporated:
=SUMIF(A4:F4,">="&LARGE(A4:F4,H4))
See the attachment.Microsoft MVP  Excel