You can just change “Z” and “ZZ” to “ZZZ” using the rule engine (or expression node of your choice), then do a GroupBy node and sum your “Number” column while grouping by your “Brand” column.
Unless you need it to be dynamic. If so, then let us know if it would always be based off the 1st character in the Brand string column.