May 20 2022 02:12 AM
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.
May 20 2022 03:18 AM
May 20 2022 07:59 AM
@L z. 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 :)
May 20 2022 08:16 AM
No formula as everything is done with Power Query. In the attached doc. you'll find some pointers that might help...
May 20 2022 08:27 AM
@L z. 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) |
May 20 2022 09:16 AM
SolutionHere 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)"
May 20 2022 09:19 AM - edited May 20 2022 10:56 AM
See Sheet2 for a by Column option
May 20 2022 09:26 AM
May 20 2022 09:28 AM
May 20 2022 10:12 AM
@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)","")
)
May 22 2022 11:28 PM - edited May 23 2022 05:37 AM
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.
May 23 2022 03:43 AM - edited May 23 2022 03:45 AM
Is it possible to modify the formula that if E2 is equal to 1, E3 is equal to 2 and E4 is equal to 3, the total is 6, not 6(0A) ? Thank you very much for your kind assistance.
May 23 2022 04:03 AM
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.
May 23 2022 05:34 AM
May 23 2022 07:15 AM
@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
May 23 2022 01:41 PM
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.
May 24 2022 07:53 PM
May 25 2022 09:20 AM
@jukhk06 , glad it helped, thank you for the feedback
May 20 2022 09:16 AM
SolutionHere 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)"