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)"
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.
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 🙂
jukhk06 , glad it helped, thank you for the feedback