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 gamesSheet 1, Sourced Rankings

In Sheet 2 Sheet 2, Condensed/Sorted Rankings, 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 search 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