Google Sheets: (Rank-Based Voting) Trying to get "point" totals from multiple columns, where the "points" are in column A and Columns B-Z are titles

Question

I'm trying to figure out how to automate the calculations I've been doing. To be specific, I'm combining 25 different ranked lists into one list that's to be organized by the list positions of each title (rank-based voting style). I have 25 sources, each with 100 ranked indie video games

In Sheet 2

,

I used the formula:

=QUERY(FLATTEN('Indies Votes'!B4:Z103),"SELECT Col1,COUNT(Col1) where Col1 is not null group by Col1")

...to gather titles and tell me how many times they appear - BUT I'd also like Sheet 2 Column B to add together the "points" each title would receive via it's list position. Since the "points" are stationary in Column A, I'm having trouble with whatever sumif type of function I would need to put here for it to do the math for me.

For example,

Stardew Valley's points would be 91+97+94+84+94+...etc from the 17 lists it appears in and I'd like to add up all of those for each title in Sheet 2

I just can't figure out the formula to take the titles in Sheet 2 Column A, reference them against Sheet 1 Columns B-Z, then reference the number(s) in Sheet 1 Column A, and add those together for each individual title in S2CA

Long time listener, first time caller - lost without this site. you geniuses are invaluable

Answer 1

For Abzu (row 13):

=SUM(MMULT(TRANSPOSE(ARRAYFORMULA(IF('Indie Votes'!$B$4:$Z$103=$A13,1,0))),'Indie Votes'!$A$4:$A$103))

Answer 2

Try this out:

=SUMPRODUCT(IF('Indies Votes'!B4:Z103="Stardew Valley",'Indies Votes'!A4:A103))

Demo

Answer 3

You may try with the below formula. I hardcoded Stardew Valley for representation but you can point it to the respective cell in Sheet 2 Column A :

=sum(bycol('Indies Votes'!B4:Z103,lambda(Σ,ifna(filter('Indies Votes'!A4:A103,Σ="Stardew Valley")))))