You will need this link to solve this:

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:

CodePudding user response：

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.