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)"
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.
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 if you don't have 365 that would be why my option didn't work. Here is a slightly 'shorter' version of Sergei's:
=SUM(--LEFT(C2:C4, FIND("(",C2:C4&"(")-1 ) ) &
IF( SUM(--IFERROR(FIND("(",C2:C4),0)),
"(" & SUM(--IFERROR(MID(LEFT(C2:C4,LEN(C2:C4)-2), FIND("(",C2:C4)+1,99), 0)) &"A)",
"" )but if his didn't work on your other PC this one probably won't either.
In the attached is both formulas working on my PC