We all love to add things up. Its always good if things add up to the way we want. And no doubt from Excel newbie to pro everyone uses SUM formula. We have recently discussed how to do conditional SUM as well where we learnt to add up only those values that certain criteria.
However, we may wish to add up only the top 2 or 3 or 10 or any N values while doing calculations on simple data range to complex data using pivot tables. Following methods help you understand how different functions / formula in Excel help you get the total of top or first ‘N’ values in data set.
Again this is a fairly large post and with animations it grew even more. So it has been divided in four pages. Don’t forget to check out other methods on next pages.
Just to clear out confusion if there is any, the following methods are based on simple numbers but you can use the calculation on Dates as well with a little to no additional effort. So try out the methods on dates as well and tweak up the formula a little if needed.
Method 1: Going to the top one LARGE step at a time
LARGE function let you find the nth largest value in the data set. Meaning it can find you the largest, second largest, third largest or 10th largest or whatever largest value you want, it is very easy.
Syntax of LARGE function is:
=LARGE(array,k)
Array is collection of item out of which you are trying to find out the nth largest value. This can be a simple data range or a named range.
Where K acts as a point of reference and is said as kth largest or nth largest value to be found. For example in a collection of three items 10, 11 and 12 the largest is 12 for which K=1. For second largest i.e. K=2 the value is 11.
Knowing this, if you want to know the sum of the top 3 values in a data range you can do so using following steps:
Step 1: Open the file and go to tab named:
Step 2: Observe the given information, your data is in range. In cell put the following formula and press Enter key
=large(range,1)+large(range,2)+large(range,3)
Bonus Tip - Using named ranges
If your data is large then selecting the range every is a peril in the way of efficiency so select the range once and go to name box and type any name you want to give to data range. In our case our range is Sales column and we named it: numbers.
The other column of is Sales person. Select the data of that column as well and name it: salesperson. We are going to need that later.
This way formula writing will be much easier and saves you selection time and errors in selection. With name defined your formula will become:
=LARGE(numbers,1)+LARGE(numbers,2)+LARGE(numbers,3)
Method 2: Making large small - Butterflies all the way!
Well its quite manageable if you are summing up the top three values but what if you want to have a sum of top five values? You will have to write the formula whole five times. Now this is seriously going to enLARGE! So cut the crazy bit out by changing the formula to this:
=SUM(LARGE(numbers,{1,2,3}))
The above is an array formula we have mentioned how many top values to be summed in an array fashion (look closely we have curly brackets). That part of the formula i.e. LARGE function, finds the top three values from the data set. To confirm select the LARGE(numbers,{1,2,3}) part of the formula and press F9 key and you will get:
{299,298,297}
Press Ctrl+Z to have the LARGE function back in place and press Enter
