Forum Discussion
Tallying totals for items in columns based on a value in column A (scorecard)
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.
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 Replies
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)))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))- You're welcome! Glad it worked.
