Google Sheets: SUM multiple numbers in same CELL based on Word

Question

I want to calculate SUM how many items sold, the sold items are encased in brackets, example: 1st Product Title [ 1 ] (for this product 1st Product Title the customer buy only 1 item so how can SUM quantity sold for each product even if there are many products recorded in the same CELL:

Sheet Link (you can edit it to help me): https://docs.google.com/spreadsheets/d/1xoNPfIfrVv7jF8W3Pa67Iu9oJRz2NOncW7oLQ94mWQ4

Answer 1

Please use this formula in cell B2:

=TRANSPOSE(QUERY({ArrayFormula( 
                   {REGEXEXTRACT(QUERY(flatten(ArrayFormula(IFERROR(SPLIT(A3:A,",")))),"where Col1 <>''"),"[^\[]+"), 
                    REGEXEXTRACT(QUERY(flatten(ArrayFormula(IFERROR(SPLIT(A3:A,",")))),"where Col1 <>''"),"\[(\d+)")*1}) 
                }, "select sum(Col2) group by Col1 label sum(Col2) '' "))

Functions used:
+ TRANSPOSE
+ QUERY
+ ArrayFormula
+ REGEXEXTRACT
+ flatten Undocumented
+ IFERROR
+ SPLIT

Answer 2

Enter following formula in B2 and Copy it from B2 to D5

=IF($A2="","",IFERROR(MID($A2,(FIND("[",$A2,FIND(B$1,$A2,1))+1),(FIND("]",$A2,FIND("[",$A2,FIND(B$1,$A2,1))+1)-1)-(FIND("[",$A2,FIND(B$1,$A2,1))+1)+1),0)*1)

Edit: As per your comment below, adding formula for Price Columns. Just replace "[" with "€", and reference to the top cells in the columns.

=IF($A3="","",IFERROR(MID($A3,(FIND("€",$A3,FIND(B$1,$A3,1))+1),(FIND("]",$A3,FIND("€",$A3,FIND(B$1,$A3,1))+1)-1)-(FIND("€",$A3,FIND(B$1,$A3,1))+1)+1),0)*1)