Function Problems

How to Calculate Excel Rankings Using Multiple Criteria Across Branches

Camila Milosovich Sep 28, 2026 869 views

Question details

The user needs to dynamically rank students within a specific class across multiple organizational branches, sorting them by Total score, Mathematics, Physics, Chemistry, and Biology in descending order.

How to Calculate Excel Rankings Using Multiple Criteria Across Branches

Product: Spreadsheet
Device & OS: not provided
Scenario: Filtering data to isolate a specific class across multiple branches, and then ranking those students based on multiple subject criteria dynamically.
Observed behavior: The user wants to replace manual multi-level sorting with a dynamic formula that automatically calculates and populates the ranked results based on up to five criteria.

Before you start

Ensure your dataset is organized in a flat tabular structure with clear column headers (e.g., Class, Branch, Name, Total, Math, Physics, Chemistry, Biology) and contains no merged cells within the data range.

Solution 1Recommended

Use Dynamic Array Formulas (FILTER and SORTBY)

This is the ideal approach for generating a dynamic, auto-updating ranking table that automatically handles multiple criteria without requiring manual sorting each time data changes.

By combining the modern FILTER and SORTBY functions, you can extract the specific class data and simultaneously rank it based on multiple subjects. This creates a separate, dynamic list that updates instantly when the source scores change.

Select the output cell

Click on the top-left cell of the blank area where you want the dynamic ranked list to appear.

Apply the FILTER function

Start by writing a FILTER function to isolate the required class. For example, if your data is in A2:H100 and Class is in column A: =FILTER(A2:H100, A2:A100="Class 6").

Wrap with the SORTBY function

Wrap the FILTER formula inside a SORTBY function to apply the multi-level sorting. The syntax will look like: =SORTBY(FILTER(A2:H100, A2:A100="Class 6"), [Total Range], -1, [Math Range], -1, [Physics Range], -1, ...).

Adjust arrays to match filtered data

Since you are sorting a filtered array, ensure your SORTBY arrays match the dynamically filtered data, or apply the FILTER function to each sorting criteria array directly inside the formula.

Press Enter to generate results

Press Enter. The formula will automatically spill the results into the adjacent cells, perfectly filtered by class and ranked descending by the five specified subjects.

Use Dynamic Array Formulas (FILTER and SORTBY)

Solution 2

Use Built-in Filter and Custom Sort

A straightforward, manual method that utilizes the standard Custom Sort tool. Best used for one-off analyses where dynamic updating is not required.

Advanced Data Analysis Simplified

Rank and Analyze Data Dynamically with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for modern dynamic array formulas like SORTBY and FILTER, making it effortless to build multi-criteria rankings. Say goodbye to manual sorting and handle complex cross-branch data analyses in seconds.

1. Open your dataset: Launch WPS Spreadsheet and open your multi-branch student data file.
2. Select an output location: Choose a blank area on your worksheet or create a new sheet for the ranking report.
3. Enter the formula: Type your combined =SORTBY(FILTER(...)) formula to instantly fetch and rank the specific class data based on your 5 subjects.
4. View dynamic results: Press Enter. Your rankings will instantly populate and automatically update whenever student scores change.

Fully compatible with Microsoft Excel (.xlsx) formats and advanced array formulas.Lightweight processing engine that effortlessly handles massive datasets.Intuitive Custom Sort interface for quick multi-level data arrangement.Free to download and use with comprehensive data analysis tools out of the box.

microsoft office alternative - wps office

Frequently Asked Questions

How does multi-criteria sorting handle duplicate scores?

When sorting by multiple criteria, the system uses subsequent levels as tie-breakers. If two students have the exact same 'Total' score, the system checks the next criteria (e.g., 'Mathematics') to determine who ranks higher. This continues down the list of subjects until the tie is broken.

Why is my SORTBY formula returning a #NAME? error?

The #NAME? error typically occurs if your spreadsheet software does not support modern dynamic array functions. Ensure you are using an updated version of WPS Office or Microsoft 365, which fully support functions like SORTBY and FILTER.

Can I add a rank number column instead of sorting the whole table?

Yes. Instead of physically reordering the rows, you can add a helper column and use the COUNTIFS function to calculate a student's rank based on multiple conditions across branches. However, building a COUNTIFS formula for 5 different tie-breaking subjects can become highly complex, making the dynamic SORTBY method much more efficient.

Excel & Data Problems