How to group by, regardless of a extra plus character at the end, with query?

Question

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

Answer 1

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 google sheet