SOLVED

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

Occasional Contributor

# 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.

17 Replies

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

With Power Query (file attached):

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

@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

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

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

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

@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)
best response confirmed by jukhk06 (Occasional Contributor)
Solution

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

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

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

See Sheet2 for a by Column option

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

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

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

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

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

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

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

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.

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

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.

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

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.

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

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.

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

@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

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

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.

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

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

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

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