Tallying totals for items in columns based on a value in column A (scorecard)

[jjelliott]

Greetings Excel community. 

I am very much still learning and am a bit stuck on how to accomplish this.

I have values in column A of a spreadsheet, 1-15.  In columns B-K there are categories. In each category a name will appear for each of the 1-15 values.  My goal is to total the 'score' for each name based on the corresponding value in column A.

For instance in this example using only 1-3 rather than 1-15, Tom would have 16, Sue would have 21 and Anne would have 23  (with the correct numbers now)

1	Tom	Sue	Sue	Tom	Tom	Sue	Sue	Tom	Sue	Anne
2	Sue	Tom	Tom	Anne	Anne	Tom	Tom	Sue	Tom	Tom
3	Anne	Anne	Anne	Sue	Sue	Anne	Anne	Anne	Sue	Sue

Any help would be greatly appreciated.

[#2 · Accepted solution]

@jjelliott 

There's a few ways to do it depending on your version. BTW, you may want to check your sample Tallies.  Tom is correct but the others are off.

Non-365 solution (Ctrl+Shift+Enter array):

=SUM((names=E6)*(ROW(names)))

365:

=REDUCE(0,names,LAMBDA(a,v,IF(v=E6:E8,a+ROW(v),a)))

[#3]

@Patrick2788 Thank you very much!  This was perfect.  I was not aware of the ROWS function.  The only change I needed to make was adjusting the count because I had a header.  Initially this was confusing because I thought that ROWS was counting the rows used not the rows from the first row.

=SUM((names=E6)*(ROW(names)-1))

[#4]

You're welcome! Glad it worked.