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.

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

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:

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

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel