Function Problems

How to Count Top 3 Rankings by Participant in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a method to count how many times each of the five participants achieves a top-three rank across five activities without calculating individual ranks for every single activity manually.

Product: Excel
Device & OS: not provided
Scenario: Analyzing multiple activity scores to determine the frequency of top-three finishes for a group of participants.
Observed behavior: The user wants to streamline the process by calculating the top-three counts directly from the raw score table (B2:F6) without creating separate auxiliary rank columns for each activity.

Before you start

Verify that your data range (A2:F6) is free of merged cells and ensure that any tied scores are accounted for, as Excel's default ranking behavior may impact tied top-three results.

Solution 1Recommended

Count Top Rankings Using Power Query

Power Query allows you to reshape the data, calculate ranks efficiently, and group the results to find top-three finishes without complex formulas.

Unpivoting your dataset transforms a two-dimensional grid into a flat tabular format. This makes it significantly easier to group the data by activity, calculate relative ranks, and then summarize the occurrences by participant.

Load Data to Power Query

Select your data range (A1:F6) and navigate to the Data tab on the ribbon, then choose 'From Table/Range' to open the Power Query Editor.

Unpivot Score Columns

Select the Participant name column, right-click the column header, and choose 'Unpivot Other Columns'. This flattens your matrix into Participant, Activity, and Score columns.

Calculate Activity Ranks

Group the data by the Activity column and add a custom step to calculate the rank of each score descending within its respective activity group.

Filter for Top 3 Ranks

Click the filter dropdown on your newly calculated rank column, choose Number Filters, and apply a rule to keep only values less than or equal to 3.

Count by Participant

Finally, group the filtered table by the Participant column and select 'Count Rows' as the operation. Close and Load the results back to your worksheet.

Highly Scalable: This method automatically adapts if you add more participants or activities to your source table in the future.

Solution 2

Calculate Counts with Dynamic Array Formulas

For users with newer versions of Excel, dynamic array functions can process the entire matrix directly in the worksheet.

Powerful Data Analysis

Easily Count Rankings with WPS Spreadsheet

Use WPS Spreadsheet's robust data processing capabilities and modern array functions to analyze and rank scores effortlessly without cluttering your workbook with helper columns.

1. Launch WPS Spreadsheet: Open your workbook containing the participant scores in WPS Office.
2. Access Data Tools: Navigate to the Data tab to utilize built-in sorting, advanced filtering, and unpivoting features.
3. Apply Array Functions: Enter dynamic array formulas directly into the grid to calculate and count rankings across multiple activities.
4. Summarize Results: Use WPS PivotTables to quickly aggregate and display the top-three rankings per participant.

Fully compatible with Microsoft Excel formulas, dynamic arrays, and formatting.Built-in advanced data processing tools to group, sort, and filter ranking data.Lightweight installation with a clean, user-friendly interface.Easily tally top-three scores across multiple activities natively.

microsoft office alternative - wps office

Frequently Asked Questions

How does Excel handle tied scores when calculating rankings?

By default, Excel assigns the same top rank to duplicate scores. For example, if two participants tie for the highest score, they both receive a rank of 1, and the next highest score receives a rank of 3.

Can I use conditional formatting to highlight the top 3 scores in each activity?

Yes. You can select the column containing the activity scores, go to the Home tab, click Conditional Formatting, choose Top/Bottom Rules, and set it to highlight the Top 3 items.

Why should I unpivot data before ranking it in Power Query?

Unpivoting transforms a two-dimensional grid into a flat, tabular format with dedicated columns for Participant, Activity, and Score. This makes it much easier for Power Query to group the data and calculate relative ranks across specific categories.

Does WPS Spreadsheet support dynamic array formulas?

Yes, the latest versions of WPS Spreadsheet support dynamic arrays, allowing formulas that output multiple values to automatically spill into adjacent empty cells.

Excel & Data Problems