Sum Vlookup each small value for 1 string

[Excelpromax123]

Hi guys. I need the formula to add each value according to the figure described below. Please help

sumvlookup.xlsx

Sheet1 DATA,Output,For example, in cell E4 = 12, search each code then Sum again Name,Number,Name,Number,Take the number 1: search in the data table to get 50 1,50,12,120,= 50 +70,Take number 2: search in the data table to get 70 2,70,23,160,= 70+90,Result = 50 + 70 = 120 3,90,123,210,= 50+70+90 4

docs.google.com

 

[Excelpromax123]

In cell F4 I use the formula on the spreadsheet, it's ok. But when I switch to the code, it doesn't work properly, please fix the code

f4=SUMPRODUCT(ISNUMBER(SEARCH(""&$B$4:$B$12&"",""&E4&""))*$C$4:$C$12)

Sub test()
Dim rng
Set rng = Range("B4:c12")
With Range("e4:e8")
.Offset(, 1).Value = Evaluate("SUMPRODUCT(ISNUMBER(SEARCH(""&rng.Address&"",""&E4&""))*.Address)")
End With
End Sub

 

[Peter_SSs]

Looks like you have forgotten my request from a couple of days ago.

@Excelpromax123

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

In cell F4 I use the formula on the spreadsheet, it's ok.

f4=SUMPRODUCT(ISNUMBER(SEARCH(""&$B$4:$B$12&"",""&E4&""))*$C$4:$C$12)

Are you sure that formula works? If E6 was 121 instead of 123 that formula returns 120. Shouldn't it return 170 in that case?

Shouldn't it be more like the 'Mine' column?

22 08 29.xlsm
	B	C	D	E	F	G
3					Yours	Mine
4	1	50		12	120	120
5	2	70		23	160	160
6	3	90		123	210	210
7	4	150		9	300	300
8	5	40		91	350	350
9	6	30		222	70	210
10	7	160
11	8	210
12	9	300
Sheet5

Cell Formulas
Range	Formula
F4:F9	F4	=SUMPRODUCT(ISNUMBER(SEARCH(""&$B$4:$B$12&"",""&E4&""))*$C$4:$C$12)
G4:G9	G4	=SUMPRODUCT(VLOOKUP(MID(E4,ROW(INDIRECT("1:"&LEN(E4))),1)+0,B$4:C$12,2,0))