Calculation Issues

How to Calculate the Top Four or Top 15 Scores in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a method to sum only the highest N scores (such as the top 4 out of 6, or top 15) for each individual, rather than calculating the total of all available scores.

Product: Excel
Device & OS: not provided
Scenario: Evaluating performance metrics or competition scores where rules dictate that only a subset of the best results should be counted towards a person's final total.
Observed behavior: Standard PivotTables and basic SUM functions aggregate all data by default, requiring specific ranking functions, helper columns, or array formulas to isolate and sum only the top results.

Before you start

Ensure your dataset is organized with clearly defined rows for each person's scores, and verify that there are no blank cells disguised as text in your numerical ranges.

Solution 1Recommended

Use the SUM and LARGE Functions to Calculate Top Scores

An efficient formula-based approach using the LARGE function allows you to extract and sum a specific number of highest scores directly from a row or range.

The LARGE function is designed to return the k-th largest value in a dataset. By combining it with the SUM function and an array constant, you can easily add together multiple top scores without needing complex macros.

Select the target cell

Click on the cell where you want the final sum of the top scores to appear.

Enter the array formula

Type the formula =SUM(LARGE(B2:G2, {1,2,3,4})) to calculate the sum of the top 4 scores out of the range B2:G2. If you need the top 15, you would expand the array to {1,2,3,...,15}.

Apply to other rows

Press Enter to execute the calculation. Click the cell again and drag the fill handle down to apply this formula to the rest of the participants.

Dynamic Arrays: If you are using a newer version of Excel, you can utilize the SEQUENCE function to avoid typing long arrays manually. For example, =SUM(LARGE(B2:G2, SEQUENCE(4))) achieves the same result.

Solution 2

Add a Helper Column for PivotTable Aggregation

Since standard PivotTables cannot directly filter and sum only the top N scores per person dynamically, a helper column can be used to rank the data before summarizing it.

Easily Calculate and Sum Top Scores in WPS Spreadsheet

WPS Office provides a highly compatible and user-friendly spreadsheet environment where you can seamlessly use advanced array formulas like SUM and LARGE to calculate top competition scores effortlessly.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your competition scores.
2. Apply the calculation formula: Click the cell for the total, and input =SUM(LARGE(range, {1,2,3,4})) replacing 'range' with the actual cells containing the scores.
3. Fill down the formula: Press Enter to calculate the result, then double-click the fill handle on the bottom-right corner of the cell to calculate the top scores for everyone.

Fully compatible with Microsoft Excel (.xlsx) formats and array formulasCalculate top competition scores effortlessly with built-in data analysis functionsFree to use with a lightweight installation that runs smoothly on all devicesIntuitive interface for managing complex datasets and PivotTables

microsoft office alternative - wps office

Frequently Asked Questions

Can I average the top 4 scores instead of summing them?

Yes, you can easily modify the calculation by replacing the SUM function with the AVERAGE function in your array formula. For example, =AVERAGE(LARGE(B2:G2, {1,2,3,4})) will give you the average of the top four results.

How do I sum the lowest scores instead of the highest?

To calculate the sum of the lowest scores, use the SMALL function instead of LARGE. For instance, =SUM(SMALL(B2:G2, {1,2,3})) will add up the three lowest scores in the specified range.

Why is my array formula returning a #NUM! error?

A #NUM! error typically occurs if you request more top scores than there are numerical values in your range. Ensure that if you are asking for the top 15 scores, the referenced range actually contains at least 15 numeric values.

How can I make the 'N' value dynamic in my formula so I don't have to type arrays?

You can reference a specific cell for the 'N' value by combining the SEQUENCE function. For example, =SUM(LARGE(B2:G2, SEQUENCE(H1))) will sum the top scores based on whatever number you type into cell H1.

Excel & Data Problems