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
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)
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