Forum Discussion

mep156's avatar
mep156
Copper Contributor
Aug 15, 2025

Help creating weighted average ranking

Hello,

I have multiple lists of rankings of certain people and would like to create a weighted average "ranking" for each individual using those lists. I was able to do it with multiple SUMIF() and COUNTIF() functions but have to manually add in each new column as another function to the equation, and I have over 50 of these lists. Is there any way to do this a bit more elegantly? Current formula is:

 =(SUMIF($B$2:$B$11,G2,$A$2:$A$11)+SUMIF($C$2:$C$11,G2,$A$2:$A$11)+SUMIF($D$2:$D$11,G2,$A$2:$A$11))/(COUNTIF($B$2:$B$11,G2)+COUNTIF($C$2:$C$11,G2)+COUNTIF($D$2:$D$11,G2)) 

where G2 is the name of a person

 

 
excel
Excel on Mac
Formulas and Functions

2 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Aug 16, 2025

    There are couple of ways. Here is fully dynamic formula-

    =LET(fx,CHOOSECOLS,arr,DROP(REDUCE("",TOCOL(B.:.F&"|"&A.:.A),LAMBDA(a,x,VSTACK(a,TEXTSPLIT(x,"|")))),1),
GROUPBY(fx(arr,1),--fx(arr,2),AVERAGE,,0))

    Few semi dynamic and manual formulas-

    =MAP(G2:G7,LAMBDA(X,SUMPRODUCT($A$2:$A$11,BYROW(--($B$2:$D$11=X),MAX))/COUNTIFS($B$2:$D$11,X)))
=SUMPRODUCT($A$2:$A$11,BYROW(--($B$2:$D$11=G2),MAX))/COUNTIFS($B$2:$D$11,G2)

    Download the attached file.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 15, 2025

    =SUM(MMULT(TRANSPOSE($A$2:$A$11),N($B$2:$D$11=G2)))/COUNTIFS($B$2:$D$11,G2)

    This works in my sample sheet. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021 or Excel 2024.

     

Resources