Forum Discussion

jukhk06's avatar
jukhk06
Copper Contributor
May 20, 2022
Solved

How to sum the total for 1(1A) + 3(3A) + 5 = 9(4A) ?

How to sum the total for 1(1A) + 3(3A) + 5 = 9(4A) ? Other examples: 1(1A) + 3(2A) = 4(3A) 10(2A) + 1 = 11(2A) I need to sum at least 100 cells in the Excel (same column or same row). Thank you.
excel
Formulas and Functions
training
  • SergeiBaklan's avatar
    SergeiBaklan
    May 20, 2022

    jukhk06 

    Here formula could work

    =SUM(--IFERROR(LEFT(C2:C4, FIND("(",C2:C4)-1 ), C2:C4) ) & "(" &
  SUM(--IFERROR(MID(C2:C4, FIND("(",C2:C4)+1, FIND("A",C2:C4)-FIND("(",C2:C4)-1 ), 0)) &
  "A)"
jukhk06's avatar
jukhk06
Copper Contributor
May 20, 2022

Lorenzo  Many thanks for your reference on learning materials. If the data is presented in the format as shown below, can I still use Power Query to calculate the answer ? Thank you very much for your kind assistance.

 

 1(1A)7(5A)
 10 2(1A)
 3(2A)4
Answer14(3A)13(6A)
  • jukhk06's avatar
    jukhk06
    Copper Contributor
    May 20, 2022
    thanks a lot, L z. I will study the materials of Power Query too 🙂
    • mtarler's avatar
      mtarler
      Silver Contributor
      May 20, 2022

      jukhk06 alternatively I might suggest using LET to make your life easier when you apply to different ranges:

      =LET(in,O1:O3,
     n,SUM(--IFERROR(LEFT(in,SEARCH("(",in)-1),in)),
     p,SUM(--IFERROR(MID(LEFT(in,SEARCH("A",in)-1),SEARCH("(",in)+1,LEN(in)),0)),
     IF(n,n,"") & IF(p,"("&p&"A)","")
     )
      • jukhk06's avatar
        jukhk06
        Copper Contributor
        May 23, 2022

        I know why the formula was not working in the Excel installed in the computer in my office due to old Excel version as suggested by Sergei Baklan.  

         

        When I come back home and test your formula in the 365 Excel, no issues and the results are all correct. I appreciate so much for your assistance. However, I am very worried about how I can work on these formulas if I come back office to work on the old Excel version ^^" Grateful for your advice. Thank you very much.

         

         

Resources