How do I log the the sum of values from multiple rows to a new row if the value in the column next to it matches

Question

You will need this solve this.

In this sheet, I am trying to take the scores of the people and log them in the "Week Total" Column L as a sum. The names often exist in each column for that days score. I need to take their score for each day next to their name, and log it next to their name in Column L as a total if that makes sense.

So if Bob got a 10 on Monday, a 10 on Tuesday, and X the rest of the days, then I need to log that in Column L next to his name as "20".

I am using the Unique function to log all their names under Column K.

Thank you!

user_16118686 · Answer

You can use query and combine it with some array notations.

Formula:

=query({{A4:A,B4:B};{C4:C,D4:D};{E4:E,F4:F};{G4:G,H4:H};{I4:I,J4:J}}, "select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2) ''", 0)

Output:

Note:

A duplicate Bob can be seen as you have spaces after Bob on some cells (e.g. Bob ). Make sure to have no spaces with them when inputting to remove duplicates from the formula result, or just use trim before using them. See modified formula before:

Formula with trim:

=query(index({{trim(A4:A),B4:B};{trim(C4:C),D4:D};{trim(E4:E),F4:F};{trim(G4:G),H4:H};{trim(I4:I),J4:J}}), "select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2) ''", 0)

Result:

user_6827197 · Answer

You're not making your life easier by inputting X since that will be read as text, I'd recommend using 0 instead (if you insist on X then add an IF statement to exclude the Xs).

But what you're looking for is =SUMIF() coupled with some smart Array notation as demonstrated here in the documentation.

Create arrays You can also create your own arrays in a formula in your spreadsheet by using brackets { }. The brackets allow you to group together values, while you use the following punctuation to determine which order the values are displayed in:

Commas: Separate columns to help you write a row of data in an array. For example, ={1, 2} would place the number 1 in the first cell and the number 2 in the cell to the right in a new column. Semicolons: Separate rows to help you write a column of data in an array. For example, ={1; 2} would place the number 1 in the first cell and the number 2 in the cell below in a new row. Note: For countries that use commas as decimal separators (for example €1,00), commas would be replaced by backslashes () when creating arrays.

You can join multiple ranges into one continuous range using this same punctuation. For example, to combine values from A1-A10 with the values from D1-D10, you can use the following formula to create a range in a continuous column: ={A1:A10; D1:D10}.

Adding arrays to existing formulas You can also use arrays with other existing formulas using brackets in order to organize the returns from your formulas into rows or columns.

For example, ={SUM(A1:A10), SUM(B1:B10)} will produce two values. The first cell will contain the sum of A1 to A10, the cell to the right will contain the sum of B1 to B10.