You can use the SUMIF and LARGE functions to sum the top three numbers in a data range. You can change the top three to any other numbers, such as the top five or top 10 numbers.
Example: You are working with a worksheet with the product in column A, the amount in column B, and the profit/loss in column C.
Question: What is the sum of the top three numbers in column B?
=SUMIF(B2:B10,">="&LARGE(B2:B10,3))
The result returns 2,878.
Explanation:
- Step 1: LARGE(B2:B10,3): To have the third largest number;
- Step 2: ">="&LARGE(B2:B10,3): The condition to have the top three largest numbers;
- Step 3: The SUMPRODUCT function returns the sum of the products of the new array and the range B2:B10.
However, this may be problematic if the top values are more than the count you specified because of the duplicates. In this case, you will need to check the total count first and modify the formula accordingly.
=MAX(B2:B10) + LARGE(B2:B10,2) + LARGE(B2:B10,3)
Notes: The SUMIF Function
The SUMIF function sums the values in a range that meets the specified criteria.
Formula:
=SUMIF(range, criterion, [sum_range])
Explanations:
– Range is required; the cells you want to be evaluated by criteria.
– Criterion is required; the criteria define which cells will be added.