excelexcel-formulatransposesubstitutionsumifs

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


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.

enter image description here

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.

enter image description here

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


Solution

  • 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:

    enter image description here

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

    enter image description here