More eloquent solution for SUMIF of two tables, based on number of countries occurring in cell of table A, divide the corresponding number in table B

Question

I have two tables (lets say A + B). Table A includes a number of vehicles to be driven in a set of countries. Table B includes the planed driven KM per country, from Table A. (Table A and Table B are on two different Sheets)

On third sheet there is a summary, that should calculate the sum of all planed KM per country. here is where I'd like to find a "better" solution.

Currently there are more than one country per week, on occasion. To not clog up Table A or B I have created another sheet with transposed data from table A and B where I have added a column with the formula:

=LEN(Q8)-LEN(SUBSTITUTE(Q8;"-";""))

This counts the number of "-" that occurs and I then use this to divide the planed KM to get the estimate per country.

In the summary I use: =SUMIF($V$8:$V$12;""&B7&"";$Y$8:$Y$12) + SUMIF($AA$8:$AA$12;""&B7&"";$AD$8:$AD$12) + SUMIF($Q$8:$Q$12;""&B7&"";$T$8:$T$12)

This can then find a single country when there are more planed in one week. As far I can see this setup works as intended.

Question is, could this be modified to only use the data from Table A + B without the use of my transposed data?

If anyone have any good ideas?

- Markdown Data

Table A	CW12	CW13	CW15	CW16
Car1	SE	DK-DE	DE	AU
Car2	SE	FI	EE	LV
Car3	SE	DK-DE-NL	BE	FR

Table B	CW12	CW13	CW14	CW15
Car1	300	150	200	150
Car2	150	150	150	150
Car3	200	150	400	300

Summary
Countries	KM
SE
DK
FI
DE
AU
FR
NL

Transposed Data, Car 1: =TRANSPOSE(G8:J8), =TRANSPOSE(G13:J13), =LEN(Q8)-LEN(SUBSTITUTE(Q8;"-";"")), =SUM(R8/(S8+1))

Answer 1

Something like this would work:

=SUM($B$5:$E$7*N(ISNUMBER(FIND(A11,$B$1:$E$3)))/MAP($B$1:$E$3,LAMBDA(x,COUNTA(TEXTSPLIT(x,"-")))))

Result:

Or to spill result at once with all countries:

=HSTACK(UNIQUE(TOCOL((DROP(REDUCE(0,B1:E3,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"-")))),1)),3)),MAP(UNIQUE(TOCOL((DROP(REDUCE(0,B1:E3,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"-")))),1)),3)),LAMBDA(z,SUM($B$5:$E$7*N(ISNUMBER(FIND(z,$B$1:$E$3)))/MAP($B$1:$E$3,LAMBDA(x,COUNTA(TEXTSPLIT(x,"-"))))))))