SOLVED

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

Occasional Contributor

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

3 Replies
best response confirmed by jjelliott (Occasional Contributor)
Solution

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

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)))``

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

@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))``