Counting and adding multiple variables from single cell in sheets-CodePudding

I have a sheets document that has cells that users input data into. They know to input the data in a certain format; a 'number' and a 'letter', followed by a space, a SKU number, and then a comma. I'd like to have a formula that counts the amount of each 'letters' and then adds the 'numbers' for each letter.

There are only five 'letters' users can choose from; M, E, T, W, B. The data they input isn't restricted to a set order, and there isn't a limit of how much they can input, as long as it follows the aforementioned syntax.

I attached a screenshot of an example of how this should look. The yellow cell is the user inputted data, and the green cells is data created by formula.

screenshot

Or here's a link to a live version:

CodePudding user response：

I've added a new sheet ("Erik Help") with the following solution:

=ArrayFormula(FILTER( SPLIT("B E M T W", " ") & " (" & IFERROR(VLOOKUP(ROW(A1:A) & SPLIT("B E M T W", " "), QUERY(FLATTEN(SPLIT(QUERY(FLATTEN(IFERROR(REPT(ROW(A1:A) & REGEXEXTRACT(SPLIT(REGEXREPLACE(A1:A&",", "\d ,", ""), " ", 0, 1), "\D") & "~", 1*REGEXEXTRACT(SPLIT(REGEXREPLACE(A1:A&",", "\d ,", ""), " ", 0, 1), "\d ")))), "WHERE Col1 <>'' "), "~", 1, 1)), "Select Col1, COUNT(Col1) GROUP BY Col1"), 2, FALSE), 0)&")", A1:A<>""))