I have the following table,
[Example1]
| A | B | C | D |
|---|---|---|---|
| True | Category1 | 5 | Category1 |
| True | Category2 | 2 | Category2 |
| True | Category2 | 3 | Category2 |
| True | Category2 | 6 | Category2 |
And I have the following formula
=query(A26:D64;"select B,max(C),D where A = TRUE group by B, D")
So this query groups entries by (B, D) tuple.
Current result of Example1:
| A | B | C | D |
|---|---|---|---|
| True | Category1 | 5 | Category1 |
| True | Category2 | 2 | Category2 |
| True | Category2 | 6 | Category2 |
What I want to do: If there is a " " character at the end of the "D" column's entry, I want to remove " " character, and add it to the existing entry.
Expected result of Example1:
| A | B | C | D |
|---|---|---|---|
| True | Category1 | 5 | Category1 |
| True | Category2 | 8 | Category2 |
[Example 2]
| A | B | C | D |
|---|---|---|---|
| True | Category1 | 5 | Category1 |
| True | Category2 | 2 | Category2 |
Expected result of Example2:
| A | B | C | D |
|---|---|---|---|
| True | Category1 | 5 | Category1 |
| True | Category2 | 2 | Category2 |
[Example 3]
| A | B | C | D |
|---|---|---|---|
| True | Category1 | 5 | Category1 |
| True | Category2 | 1 | Category2 |
| True | Category2 | 13 | Category2 |
| True | Category2 | 3 | Category2 |
| True | Category2 | 4 | Category2 |
Expected result of Example3:
| A | B | C | D |
|---|---|---|---|
| True | Category1 | 5 | Category1 |
| True | Category2 | 20 | Category2 |
CodePudding user response：
So you want to take the max Category2 value and add it to the sum of the Category2 values?
If so the best option is to add a Column E where you check if there is a at the end of the Category column and make that unique =IF(RIGHT(D1,1)=" ",ROW(),D1)
Then run an initial query in G1 for example:
=query(A1:E20,"select B,max(C) where A = TRUE group by B,E")
And a final query in J1 to sum those results
=query(G2:H20,"select G,sum(H) group by G")
You can find an example in this 
References: