How to sum the total for 1(1A) + 3(3A) + 5 = 9(4A) ?

[jukhk06]

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.

[#2]

@jukhk06 

With Power Query (file attached):

[#3]

@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 :)

[#4]

@jukhk06 

No formula as everything is done with Power Query. In the attached doc. you'll find some pointers that might help...

[#5]

@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)

[#6 · Accepted solution]

@jukhk06 

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)"

[#7]

@jukhk06 

See Sheet2 for a by Column option

[#8]

Sergei, thank you very much for your assistance. It works well and exactly what I want :)

[#9]

thanks a lot, L z. I will study the materials of Power Query too :)

[#10]

@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)","")
     )

[#11]

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.

[#12]

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.

[#13]

@jukhk06 

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.

[#14]

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.

[#15]

@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

[#16]

@jukhk06 

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.

[#17]

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 :)

[#18]

@jukhk06 , glad it helped, thank you for the feedback