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)
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 23, 2022
        Yes, the computer at home is installed with Microsoft Office 365. Grateful if you can advise how to make it simplified.

        Also, my office uses old version of excel and I cannot copy your formula to use direct, when I copy your formula in "sum some (1).xlsx" above in another excel file (suspect not 365, the result becomes #VALUE). However, it is fine if I copy the formula to another Excel file which is Microsoft 365 in the computer at home. Grateful for your advice how to handle the issues for the old excel version used in my office. Thank you very much for your patience and kind assistance.
  • 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 🙂

Resources