Forum Discussion
How to sum the total for 1(1A) + 3(3A) + 5 = 9(4A) ?
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 Many thanks for your swift response. The results are exactly what I want. May I know if there is any reference link that I can learn the Power Query ? I seem not see any formula in the cell. Thank you very much for your patience and advice 🙂
No formula as everything is done with Power Query. In the attached doc. you'll find some pointers that might help...
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 Answer 14(3A) 13(6A) - thanks a lot, L z. I will study the materials of Power Query too 🙂
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)"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.