How to perform aggregate function, possibly using VLookup

[jammerules]

Hello, experts -

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

I have a table like below (link in next post). 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 "AMB", I need to aggregate [2], [1], [1] (from three sections (blue bars). So, the final output should be "AMB [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!

 

[jammerules]

Here is the link to the excel document >>> Click Me

 

[KRice]

Here is one approach:

MrExcel_20220626.xlsx
	A	B	C	D
1			AMB	AMB [4]
2	Select Sprint Cycle		Balu	Balu [1]
3	Sprint ID		YK	YK [3]
4	Report Last Run		RK	RK [3]
5			OM	OM [2]
6	Assignee		KO	KO [4]
7
8	[Venus_PI1-5] Apr-26 - May-16
9	Balu [1]
10
11	AMB [2]
12
13
14	YK [2]
15
16
17	RK [2]
18
19
20	OM [1]
21
22	KO [2]
23
24
25	[Venus_PI1-5] Apr-26 - May-16
26	AMB [1]
27
28	[Venus_PI1-5] Apr-26 - May-16
29	AMB [1]
30
31	JB [1]
32
33	YK [1]
34
35	RK [1]
36
37	OM [1]
38
39	KO [2]
40
41
Sheet7

Cell Formulas
Range	Formula
D1:D6	D1	=TEXTJOIN("",0,$C1," [",SUM(SUBSTITUTE(SUBSTITUTE(FILTER($A$1:$A$50,LEFT($A$1:$A$50,LEN($C1))=$C1),$C1&" [",""),"]","")*1),"]")

 

[jammerules]

Hello, KRice -

Thanks for your help. Does your solution work with a macro? My apologies that I have not mentioned this in my original post earlier but all the values starting from A9 are dynamically populated via VBA. So, your solution/formula should be called or plugged in to a macro code. Is it possible? If so, how would I go about it?

 

[KRice]

There are a couple of things to consider. It wasn't clear how the formula is to "know" which Assignee code to look for. In my example, I simply typed those that I saw in column A. In practice, what do you do? That needs to be passed to the formula, unless you want to automatically generate a list of unique assignees (sans the square brackets/numbers)...shown below. Mingling text and numbers, especially when the numbers are to be treated as such for other mathematical operations, is generally a bad idea. That requires messy formulas to separate the text from the number, and in this case, you want to repackage the text and resultant number for the final answer. The formula presented should work on results populated in A9 and below by VBA, but you will need to ensure the range referenced by the formula is sufficiently large. In this example, I expanded it to A100. I do not think the formula will work "as is" if placed in a VBA script, even if using the Application.Evaluate("formula text") method, but I would seek out expert advice from others. Since there seems to be heavy reliance on VBA to generate this information, it would probably be better to perform this calculation in VBA, perhaps even before the text and number are combined.

You may want to create a new post with the VBA code shown and link back to this thread to illustrate what the column A content looks like and how you would like to aggregate common assignees and sum the numbers in square brackets. Someone may be able to offer a solution generated directly and entirely with VBA. Here the list of assignee inputs is automatically created with a formula, and then the formula previously presented is used to generate your final output.

MrExcel_20220626.xlsx
	A	B	C	D
1			Assignee (short)	Aggregated sum
2	Select Sprint Cycle		Balu	Balu [1]
3	Sprint ID		AMB	AMB [4]
4	Report Last Run		YK	YK [3]
5			RK	RK [3]
6	Assignee		OM	OM [2]
7			KO	KO [4]
8	[Venus_PI1-5] Apr-26 - May-16		JB	JB [1]
9	Balu [1]
10
11	AMB [2]
12
13
14	YK [2]
15
16
17	RK [2]
18
19
20	OM [1]
21
22	KO [2]
23
24
25	[Venus_PI1-5] Apr-26 - May-16
26	AMB [1]
27
28	[Venus_PI1-5] Apr-26 - May-16
29	AMB [1]
30
31	JB [1]
32
33	YK [1]
34
35	RK [1]
36
37	OM [1]
38
39	KO [2]
40
41
Sheet7

Cell Formulas
Range	Formula
C2:C8	C2	=LET(assignees,LEFT(A9:A100,SEARCH("[",A9:A100)-2),UNIQUE(FILTER(assignees,NOT(ISERROR(assignees)))))
D2:D8	D2	=TEXTJOIN("",0,$C2," [",SUM(SUBSTITUTE(SUBSTITUTE(FILTER($A$9:$A$100,LEFT($A$9:$A$100,LEN($C2))=$C2),$C2&" [",""),"]","")*1),"]")
Dynamic array formulas.