# Solved Total three largest values from a selection of cells Part 2

October 1, 2013 at 10:25:30
Specs: Windows 7
 =SUM(LARGE(C20:F20:I20:L20,{1,2,3}))This work the worksheet as required, however the selected cells may number up to 50I have tried the following but know that I am missing something, but I can't see what=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:C2"))))C2 is a volatile number, subject to change given a specific value from elsewhere in the worksheet, so I could be looking for the total of the largest 2 values, or the total of the largest 12 values.message edited by tonygibb

See More: Total three largest values from a selection of cells Part 2

October 1, 2013 at 14:05:06
 =IF(\$C\$2=1,SUM(LARGE((C19,F19,I19,L19),{1})),IF(\$C\$2=2,SUM(LARGE((C19,F19,I19,L19),{1,2})),IF(\$C\$2=3,SUM(LARGE((C19,F19,I19,L19),{1,2,3})),IF(\$C\$2=4,SUM(LARGE((C19,F19,I19,L19),{1,2,3})),0))))Thank you so much for your reply, I understand your explanation, but the method of CTRL,Shift,Enter would tend for the end User to get confused.My intention is that they are to only to create the value for C2 and that the calculations take place without any other key motion.I have created the above nested IF function which will do what I require.I just have to amend it slightly to accommodate the number of values to be Sum totalledAgain thanks for your help

#1
October 1, 2013 at 11:22:39
 This first I see is a major difference in the ranges you are referencing.This formula references 2 individual ranges: "C20:F20" and "I20:L20"=SUM(LARGE(C20:F20:I20:L20,{1,2,3}))In other words, it will sum the 3 largest values in C20, D20, E20, F20, I20, J20, K20, and L20.This formula references 4 individual cells: C20, F20, I20 and L20.=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:C2"))))There's a big difference between using commas and using colons.Before we go any further, can you explain why one formula uses colons and the other uses commas? The reason I ask is because you say that C2 could be from 2 to 12. You can't get the 12 largest values from a range of only 4 cells.

Report •

#2
October 1, 2013 at 11:39:47
 Sorry about that,=SUM(LARGE(C20:F20:I20:L20,{1,2,3}))should have read=SUM(LARGE((C20,F20,I20,L20),{1,2,3}))I have only included 4 selected cells but because of the variation in what I have, there may be up to 50 selected cells, so I have done this to keep it shortC2 is a value generated by another part of the worksheet. This will not exceed the number of selected cells, but will request the total of the largest values to that value.=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:C2"))))So what am I missing. I still cannot see what is probably a very simple answer.

Report •

#3
October 1, 2013 at 12:36:02
 re: I still cannot see what is probably a very simple answerActually, the answer is not all that simple. In fact, I'm a bit confused myself, but I think I got it to work.This formula seems to work when entered as an array formula with Ctrl-Shift-Enter:For this explanation, assume there is a 3 in C2.=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:"&C2)))) Ctrl-Shift-EnterThere were 2 issues with your formula:1 - The INDIRECT function:The reference to C2 has to be pulled out from the text string argument of the INDIRECT function in order for the actual value in the cell to be picked up by the ROW function.INDIRECT("1:C2") will not work because "1:C2" is not a text string that INDIRECT can use as a reference. "1:" & C2 becomes 1:3 which works fine.2 - The use of the array for the k argument of the LARGE function.This part is interesting and I can't really explain why it is happening, but here is what I found.If I use this formula, not entered as an array formula it only "SUMs" the single largest value. In other words, it is not using {1,2,3} as the k argument, it just uses the 1.=SUM(LARGE((C20,F20,I20,L20),ROW(INDIRECT("1:"&C2))))However, if I highlight ROW(INDIRECT("1:"&C2)) and press F9 (which evaluates that part of the formula directly in the formula bar) it returns {1;2;3} . Note the semi-colons, not commas. I then see this:=SUM(LARGE((C20,F20,I20,L20),{1;2;3}))If I hit Enter, this formula does indeed SUM all three values. What that tells me is that when the formula is not array entered, the result of ROW(INDIRECT("1:"&C2)), namely {1;2;3}, is not being picked up as an array. However, when Ctrl-Shift-Enter is used, it becomes an array for the SUM(LARGE...)) functions to use.Interesting...

Report •

Related Solutions

#4
October 1, 2013 at 14:05:06