I have the following table,

[Example1]

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:

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:

[Example 2]

Expected result of Example2:

[Example 3]

Expected result of Example3:

CodePudding user response：

So you want to take the max Category2 value and add it to the sum of the Category2 values?

If so, one 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:

• How to Combine Two Query Results in Google Sheets

• How to Use Nested Queries in Google Sheets