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)"
thanks a lot, L z. I will study the materials of Power Query too 🙂
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)","")
)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.