Add Numbers at End of Cells Based on Letters at Start (e.g. AB [4])

Hello, experts -

I am using Microsoft Excel 365. Here's is my question:

I have a table like below. I need to look up in column A for each of the "Assignee name" and collect the number within the parenthesis at the end of the name and find the sum. So, from the example below, for the name "AB", I need to aggregate [2], [1], [1] (from three sections (blue bars). So, the final output should be "AB [4]". I need to do the same for the remaining names. Can I use VLookup? Doesn't have to be that function... anything that could help me get the output is great. Thanks, folks!

Attachment 785165

Please update your profile to reflect your version of Excel, not Windows.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

1. Read the yellow banner.

HOW TO ATTACH YOUR SAMPLE WORKBOOK:
jammerules Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

2. Update your profile and then you won't have to mention it in every thread.

I have updated my profile. It should now show the Excel version. File Attached below.

This proposal employs a helper column (G) which may be moved and/or hidden for aesthetic purposes and which is populated using: =IFERROR(IF(ISTEXT(A9),MID(A9,SEARCH("[",A9)+1,1)+0,""),"")
The formula to produce the output (see cell J9) is: =I9&" ["&SUM(IFERROR((G$9:G$42)*(ISNUMBER(SEARCH(I9,A$9:A$42))),0))&"]"
Note that cell I9 contains the Assignee name.
Let us know if you have any questions.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I will do it for you this time..)