Точно, все дело в диапазоне условий. Пришлось задать конкретную длину диапазона и в формуле среднего, и в формуле смещения. Если они одинаковые, все работает.  
Спасибо всем!!!

Ой.

Присоединяю файл, где более подробно описываю. Задача на самом деле решается и без формул, с помощью сводной таблицы. Но нужна была формула. Почему же все-таки СРЗНАЧЕСЛИМН ИЛИ СУММЕСЛИМН не работает, если используется в диапазоне условий формула смещения?  
Даже если просто нужный столбец написать формулой смещения с конкретными цифрами, всё, не работает.

Не поняла, а суммпроизв к чему?

Нужно выполнение нескольких условий, в примере просто одно. Поэтому нужна именно формула CУММЕСЛИМН И СРЗНАЧЕСЛИМН. Кроме того, диапазон условий может меняться. Я указала это в файле. Это могут быть разные столбцы. Без формулы смещения все работает.

Еще раз прикрепляю файл. Формула действительно СРЗНАЧЕСЛИМН стоит. Но суть не в этом, нужна и сумма, и среднее значение, удовлетворящее нескольким условиям.  
Не работает ни та, ни та.

Спасибо всем огромное! Способ от Vikttur - супер! Все просто. Тоже думала использовать ранг, не додумалась добавить счетесли.

Ой. Еще раз.

Прикладываю файл.

Добрый день! Помогите решить задачу.  
Нужно с помощью формулы отсортировать данные столбца А по их значениям в столбце В  
Должно получиться:  
A В  
Марка 1 1 Марка 5 4  
Марка 2 3 Марка 2 3  
Марка 3 1 Марка 4 2  
Марка 4 2 Марка 1 1  
Марка 5 4 Марка 3 1  
 
Пыталась сделать с помощью ВПР и Наибольший, не получилось, так как значения в столбце B могут повторяться.  
 
Заранее спасибо.