Forum Discussion
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.
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)