its an array formula I believe, but I can’t get it to enter right
It would help us to help you you say what formula you are trying to use, and how you are attempting to enter it. You might add your OS and LO details, in case they are relevant for the answer.
Are there duplicatad numbers in the n largest numbers? How to handle the duplicates?
Sample: 1, 2, 3, 3, 4, 4, 5, 5, 5
n = 3
Result1: 5+5+5= 15
Result2: 5+4+3= 12
A variant of the formula for duplicate values (what @Zizi64 called “Result 1” in his comment):
=SUMPRODUCT(LARGE(DataList;COLUMN($A$1:INDEX($1:$1;MIN(countValues;COUNT(DataList))))))
Let’s construct this formula together, step by step.
Let’s start by preparing the test data. Let’s fill an arbitrary range (for example, C4:P4 is 14 cells) with the formula =RANDBETWEEN(0; 9). Replace the formula with the calculated values and get a set similar to
{6, 4, 0, 1, 1, 4, 4, 3, 4, 2, 9, 8, 7, 2}
Sorted in descending order it will be
{9, 8, 7, 6, 4, 4, 4, 4, 3, 2, 2, 1, 1, 0}
Great, we have several fours - let’s choose a count of number to sum so that we can only count two of them - 6
9, 8, 7, 6, 4, 4, 4, 4, 3, 2, 2, 1, 1, 0
Let’s write this number 6 into some cell and refer to it instead of using a constant in the formula.
To work with abstract names, and not with specific addresses of cells and ranges, let’s create two named ranges, as @keme did - TheList and TheCount
Getting a sorted list is simple
{=LARGE(TheList;COLUMN($1:$1))}
It’s rude and messy, the result of this action will be 9,8,7,6,4,4,4,4,3,2,2,1,1,0,#VALUE!,#VALUE!,#VALUE! … …
This shouldn’t bother us, we only need the first values from this array, and they are good.
An attempt to get the first TheCount values (you remember, we have 6) using OFFSET() is unlikely to be crowned with success. It would work great if we were to store intermediate results in helper cells in the worksheet. But we don’t want to occupy additional cells with intermediate calculations.
Let’s use INDEX():
{=LARGE(TheList;COLUMN($A$1:INDEX($1:$1;TheCount)))}
and get result 9,8,7,6,4,4
This can already be summed up using SUM() and Ctrl+Shift+Enter or using SUMPRODUCT() so as not to create an array formula
But let’s stay here for a minute. What happens if TheList is greater than the total number of values in TheCount? That’s right, we’ll get an error instead of a sum. Let’s limit the number of values to SUM with the smaller of these two values - not TheCount but MIN(TheCount;COUNT(TheList)). And we get the formula this text starts with:
=SUMPRODUCT(LARGE(TheList;COLUMN($A$1:INDEX($1:$1;MIN(TheCount;COUNT(TheList))))))
and get result 9+8+7+6+4+4=38
PS. A few more words about the construction COLUMN($A$1:INDEX($1:$1; CountValues)). It just gives a sequence of integers 1,2,3 ... which can be used in various formulas.
There are many solutions on the internet that use the ROW() function in this expression and the address of the range $A:$A. As far as I understand, this is only because to write ROW you need to press fewer buttons than for COLUMN. However, it has been noticed that formulas with the full range A:A work very slowly, especially after the number of rows in the sheet has grown to over a million. The formula began to be accelerated, forcibly shortening the range like $A$1:$A$1000. And this negated the benefits of the short word ROW instead of the long word COLUMN.
Since there are usually much fewer columns on a sheet than rows, the option that we used in our formula is always preferable (and almost always sufficient for everyday tasks)
TheList is your list of numbers (define range, or replace with your address range)
TheCount is the number of items to sum (named cell, or replace with cell address or actual number).
If list is continuous and numbers in the list are unique:
=SUMPRODUCT(TheList;RANK(TheList;TheList;0)<=TheCount)
If there are ties (identical entries possible) you need to cater for that. Provide detail:
- Nature of your numbers (all integers, currency amounts, entered data or calculated, etc.)
- Group requirement (fixed or variable number of entries to sum, distinguish between empty cells and zero entries)
At best, edit your question to attach your file. Remember that this is a public place, so you need to remove/mangle any confidential info first.
I would only like to provide an example. It also contains a solution based on slightly different approaches for both the variants of the question.
You may use the file as a form for tests and comparisons.
ask268502sumofCountLargestNumbers_AllOrDistinct_1.ods](/upfiles/1602018657717148.ods)
In fact, if I personally needed a solution to the problem, I would write a little program. Spreadsheets arent good at that kind of task. A sequential program would also require a kind of anking, but could do it without needing to evaluate ranks and matches again and again per single number to return.