Так решили ведь уже. Даже несколько вариантов показали.
Красные уже не надо выкидывать?

Тему потерял человек, ссылку дайте )

Значит есть тот первый файл с решением? ))
Ну смотрю столбцы городов не мигрируют, значит то первое решение подходит.
Можно так чтоб не усложнять всякими поисками позиций:

А для Термеза своя:

В предыдущей теме помощь была скрыта - может поэтому? )
Чуть иначе понял задачу:
=ЕСЛИОШИБКА(СУММЕСЛИ(Лист2!$E$17:$N$17;C$2;ИНДЕКС(Лист2!$E$4:$N$8;ПОИСКПОЗ($B3;Лист2!$B$4:$B$8; ); )); )
для нескольких "заказов"
=СУММПРОИЗВ(Лист2!$E$4:$N$100;(Лист2!$E$17:$N$17=C$2)*(Лист2!$B$4:$B$100=$B15))

СУММПРОИЗВ() нормально посчитает, но она не терпит текста в суммируемом диапазоне, нужно его с ч(+...) очищать. Но в примере города расположены нестандартно, я бы лучше вручную столбцы задавал как я показал, это поспособствует проверке что в этом столбце нет других городов.Ну а если они всегда строго так расположены - один раз поставить формулы и доверяться... ))

Так и в файле выше каждый раз нужно будет диапазоны под источник править, за это кому-то и деньги платят ))

Ещё есть путь макросом найти в каких столбцах какие города, и в каком столбце напитки, но это думаю есть смысл сочинять если такие таблицы нужно анализировать несколько раз в день, и каждый раз эти столбцы в непредсказуемых местах.
Но там ещё помню была задача с красным, а это уже только глазами и руками каждый раз в формуле прописывать, или опять же макрос.

Можно в макросе через Find найти все строки где в ячейке есть "Город:", затем пробежаться по каждой и запомнить где какой город, чтоб не накосячить в результатах.
Или уж тогда и собирать данные по каждому напитку в каждом городе по каждому листу заказов, можно даже тогда делать это реже и собирать с учётом за какой день данные. Например раз в месяц запускать этот процесс по всем поступившим файлам.
Всё зависти от задачи и в каком виде бывают данные. И почему часть красная ))

- кто-то должен сесть и написать ))
Но для этого нужно знать и задачу в целом, и мелкие подробности, тратить возможно напрасно время на то что показали так себе...

Сделал версию макроса учитывающую заливку города красным.

- поменял название макроса (был конфликт, и на кнопках напутано), и адрес диапазона для массива a - города там должны быть! Именно по ним и названию напитков осуществляется связь таблиц.
Если будете тянуть ещё и "Прайс" - можно делать аналогично ещё один словарь, только не суммировать. Ну и учесть что есть объединения в шапке.
В этом файле первый код не отработает, потому что изменили сводный лист зачем-то...

А почему когда я поставил цены на ПРАЙС и потом выбираю нужный заказ и энтер после столбец Прайс становится 0

вот здесь после 0 становится формулу стирает

Ну так то формула, не было там никаких формул, и не предвиделось.
Давайте пример - сделаю.

Хотя делов всего лишь заменить Value на Formula:

Значит где-то в суммируемых ячейках текст, но его там быть не должно. И формул там быть не должно.