2

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 image description here

E_net4
  • 27,810
  • 13
  • 101
  • 139
James
  • 172
  • 1
  • 1
  • 13

2 Answers2

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)

enter image description here

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)

enter image description here

Naresh
  • 2,984
  • 2
  • 9
  • 15
  • Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/214702/discussion-on-answer-by-naresh-bhople-google-sheets-sum-multiple-numbers-in-sam). – Samuel Liew May 27 '20 at 00:17
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) '' "))

enter image description here

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

marikamitsos
  • 10,264
  • 20
  • 26
  • Hi @marikamitsos, thank you so much for your answer, I copy-paste your formula in B2 cell but it's not working? you can check the Sheet and see I got #REF! error – James May 26 '20 at 02:11
  • 1
    That is because you have still **not cleared cells `C2` and `D2` and the formula cannot be expanded**. Please clear those cells and the formula will work. – marikamitsos May 26 '20 at 02:24
  • 1
    Yes, it's working well now, thank you so much bro for your help – James May 26 '20 at 02:34
  • 1
    Glad I could help :) – marikamitsos May 26 '20 at 02:39
  • While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – eirikdaude May 26 '20 at 07:39