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)"
Lorenzo's avatar
Lorenzo
Silver Contributor
May 20, 2022

jukhk06 

 

No formula as everything is done with Power Query. In the attached doc. you'll find some pointers that might help...

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)","")
     )
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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 23, 2022

      Is it possible to modify the formula that if E2 is equal to 1, E3 is equal to 2 and E4 is equal to 3, the total is 6, not 6(0A) ? Thank you very much for your kind assistance.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 23, 2022

        jukhk06 

        It's like

        =SUM(--IFERROR(LEFT(C2:C4, FIND("(",C2:C4)-1 ), C2:C4) ) &
   IF( SUM(--IFERROR(MID(C2:C4, FIND("(",C2:C4)+1, FIND("A",C2:C4)-FIND("(",C2:C4)-1 ), 0)) >0,
        "(" &
        SUM(--IFERROR(MID(C2:C4, FIND("(",C2:C4)+1, FIND("A",C2:C4)-FIND("(",C2:C4)-1 ), 0)) &
        "A)", "" )

        That could be simplified if you are on Excel 365 or 2021.

    • jukhk06's avatar
      jukhk06
      Copper Contributor
      May 20, 2022
      Sergei, thank you very much for your assistance. It works well and exactly what I want 🙂