SOLVED

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

Occasional Contributor

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)

 

1Tom SueSueTom Tom SueSueTom SueAnne
2SueTom Tom AnneAnneTom Tom SueTom Tom 
3AnneAnneAnneSueSueAnneAnneAnneSueSue

 

Any help would be greatly appreciated.

 

 

3 Replies
best response confirmed by jjelliott (Occasional Contributor)
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)))

 

@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.