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)"
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.
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 , glad it helped, thank you for the feedback
- Sergei, thank you very much for your assistance. I follow your method to copy the formula and it works even in older version of Excel at my office 🙂
If you open the file and check the formula in your version of Excel it shall be in curvy brackets, means that's array formula. Select it in formula bar (brackets won't be shown now), copy, paste into another cell and enter with Ctrl+Shift+Enter.
I slightly modified mtarler formula using SUMPRODUCT, it shall work without CSE. Please see it in attached file.
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
