How to Calculate Weighted Average Rankings for Multiple Lists in Excel
Question details
The user needs a scalable way to calculate each person's average ranking across multiple list columns without manually writing new SUMIF and COUNTIF formulas every time a list is added.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing ranking data involving many columns where the dataset grows over time, requiring a dynamic solution to aggregate data accurately.
- Observed behavior
- The goal is to automatically reshape list data and calculate grouped average rankings using advanced dynamic-array functions.
Ensure you are using a modern version of your spreadsheet software that supports dynamic array functions such as MAP, LAMBDA, and BYROW, as older versions will not be able to process these formulas.
Use MAP and LAMBDA for a Semi-Dynamic Calculation
This is the recommended approach for calculating average rankings when you have a predefined list of individuals you want to evaluate.
By combining MAP and LAMBDA with SUMPRODUCT, you can iterate through a specific range of names and calculate their averages across multiple columns in one fluid motion. This removes the need for isolated, manual updates.
Create a distinct list of the individuals you want to calculate averages for in a single column (for example, cells G2 through G7).
Select the empty cell next to the first name, and enter the following formula: =MAP(G2:G7,LAMBDA(X,SUMPRODUCT($A$2:$A$11,BYROW(--($B$2:$D$11=X),MAX))/COUNTIFS($B$2:$D$11,X)))
Ensure that $A$2:$A$11 covers your ranking values and $B$2:$D$11 covers the columns containing the names. Press Enter to generate the results for the entire list instantly.

Create a Fully Dynamic Solution with LET and GROUPBY
Use this advanced method if you want the spreadsheet to automatically extract unique names and calculate their averages without requiring a manually typed list of people.
Calculate Average Ranking for a Single Person
A simpler formula if you only need to look up the weighted average rank for one specific individual rather than the entire dataset.
Calculate Complex Data Arrays with WPS Spreadsheet
WPS Spreadsheet offers powerful support for advanced dynamic array functions like MAP, LAMBDA, and SUMPRODUCT. Handle complex ranking calculations and massive datasets seamlessly without paying premium subscription fees.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the ranking data.
- 2. Select your calculation cell: Click the cell where you want your dynamic average rankings to be displayed.
- 3. Apply the array formula: Paste your dynamic array formula (such as MAP or SUMPRODUCT) into the formula bar and press Enter to instantly populate the results.

Frequently Asked Questions
Why does my dynamic array formula return a #NAME? error?
A #NAME? error typically occurs if your spreadsheet software version does not support newer functions like MAP, LAMBDA, or BYROW. Ensure you are using the latest version of your office suite, such as the newest release of WPS Office, which fully supports modern dynamic arrays.
Can I use standard SUMIF and COUNTIF instead of dynamic arrays?
Yes, but standard SUMIF and COUNTIF formulas require you to manually add new statements (e.g., +SUMIF(...)) every time you add a new column or list. Dynamic arrays simplify this by evaluating the entire range at once, making them much better for scalable datasets.
What does the double hyphen (--) do in these formulas?
The double hyphen, or double unary operator, forces a spreadsheet to convert boolean values (TRUE and FALSE) into numeric values (1 and 0). This is necessary because math functions like SUMPRODUCT and MAX cannot calculate text-based TRUE/FALSE results directly.




