Суммирование ячеек, пока выполняется условие - формулой

[AlWin]

Уважаемые знатоки.
Помогите, пожалуйста, решить следующую проблему с помощью формул.

Колонка «А» содержит численные значения.
Колонка «В» - определители в виде текста. Ячейки В1, В5, В12 и т.д. содержат определитель "param".

Требуется для ячеек A1, А5, А12 и т.д. составить формулу суммирования диапазона от нижележащей (от А2, А6, А13) и до тех пор, пока в соседней ячейке в колонке «В» не встретится следующий "param" (до А4, А11 и тп)
Т.е. суммировать ячейки по «А», пока в ячейках по «В» выполняется условие, что они <> "param" до первого встретившегося нижележащего  "param".

Пробовал решить: An=СУММ(An+1:Am-1) c помощью ПОИСКПОЗ по колонке В, но у меня не получается:
1.   Вместо "n" вставить в тело формулы значение, возвращаемое функцией СТРОКА()
2.   Вместо "m" вставить в тело формулы значение, возвращаемое функцией ПОИСКПОЗ("param";Bn:B1000;0) , да и условный конец диапазона «В1000» в последней формуле также хотелось бы заменить на что-то более серьезное

[iron priest]

пример

[AlWin]

в упрощенном виде как-то так...

[iron priest]

Код Выделить Развернуть

=СУММПРОИЗВ((B2:$B$18<>$B$1)*A2:$A$18)-СУММПРОИЗВ((B2:$B$18=$B$1)*A2:$A$18)

[AlWin]

Видно не очень у меня получается объяснить, что хочу.
Мне не нужна формула для A19. Мне нужна формула для ячеек А1, А5, А12, которая бы сама определяла необходимый (напр. А13:А18 для ячейки А12) диапазон для суммирования и подставляла его в функцию суммирования (напр. как в колонке Е).
Упсс! Исправляю глупости в колонке Е и прикладываю повторный файл примера

[iron priest]

...

[Serge 007]

Вариант в отдельном столбце

[AlWin]

Большое спасибо за участие и iron priest и Serge 007.
Попробую вникнуть и в формат записи и (если получится) в смысл предложенных формул.
Однако в обоих случаях формулы не универсальны для разных ячеек и не могут самостоятельно определять диапазон суммирования (требуется вручную указывать диапазон для каждой вычисляемой ячейки).
Хотя нижнюю границу диапазона вполне можно закрепить ячейками $A$18 и $B$18, а параметр «param» закрепить за ячейкой $B$1 (как у iron priest).
Вот если б и текущая строка ( для ячеек В1,B2, В5, В6, В12, В13) в формуле задавалась выражением типа «В&СТРОКА()» или «В+СТРОКА()» (как у Serge 007), но такая запись не работает.

[Serge 007]

...требуется вручную указывать диапазон для каждой вычисляемой ячейки...

На основании чего сделан такой неожиданный вывод?

Ещё вариант:

Код Выделить Развернуть

=ЕСЛИ(B1="param";СУММ(ИНДЕКС(A1:A$19;ПОИСКПОЗ("param";B1:B$19;)):ИНДЕКС(A1:A$19;ПОИСКПОЗ("param";B2:B$19;)));"")

[AlWin]

Уважаемый Serge 007.
Действительно, нужно было попробовать копи-паст.
Однако меня сильно не устраивала сползающая вниз нижняя граница диапазона. Поэтому в ячейке Н1 я заменил B18 на $B$18. Затем скопировал и вставил формулу в Н12. Получил #Н/Д. Правда, методом тыка заменил $B$18 на $B$19 и проблема как-бы решилась.
Спасибо за вторую формулу. Кажется гораздо более понятной.
Однако ни первая, ни вторая формула не хотят работать в требуемых по изначальному условию ячейках - А1, А5, А12.
При копировании формул получаю циклическую ссылку...

[AlWin]

iron priest
Ну конечно же, формула копируется и работает для всех ячеек. Еще раз большое Вам спасибо (ну рабочий день у меня сегодня тяжелый)

Serge 007
Ну да, нельзя заставить работать формулы для ячеек, если они сами находятся в указанных в формулах диапазонах.
А ваши формулы - как варианты для решения в примерном виде An=СУММ(An+1:Am-1).
Тогда, если можно, помогите понять стилистику составления подобных конструкций в формулах (или помогите найти, где об этом можно почитать - чесслово я искал, но не нашел). Напр. замена В1 в Вашей первой формуле на "B"&Строка() результата не дает.

[Serge 007]

Ну да, нельзя заставить работать формулы для ячеек, если они сами находятся в указанных в формулах диапазонах.

Можно, если включить итерации

Напр. замена В1 в Вашей первой формуле на "B"&Строка() результата не дает.

Смотря какого результата Вы ожидаете
Если вставить формулу

Код Выделить Развернуть

="B"&СТРОКА()например в ячейку А1, то Вы получите вполне конкретный результат: "В1". Вот только что Вы с ним собираетесь делать? Если использовать в формуле как адрес конкретной ячейки - то необходимо использовать ДВССЫЛ(), так как ссылку, заданную текстовой строкой, в формулах можно использовать только с помощью этой функции

ЗЫ Где почитать не знаю, я формулы писать на форумах учился

[AlWin]

Еще раз огромное спасибо за конкретные ответы. Всего Вам доброго.

[Sarytai]

Вариант в отдельном столбце

простите, а в формуле

Код Выделить Развернуть

=СУММ(ДВССЫЛ("a"&ПОИСКПОЗ("param";B1:B18;)+СТРОКА()-1&":a"&ПОИСКПОЗ("param";B2:B18;)+СТРОКА()-1))*(B1="param")
нет ошибки? ведь если в первой части убрать единицу

Код Выделить Развернуть

=СУММ(ДВССЫЛ("a"&ПОИСКПОЗ("param";B5:B22;)+СТРОКА()&":a"&ПОИСКПОЗ("param";B6:B22;)+СТРОКА()-1))*(B5="param")

формула подойдет и для А столбца?

[Serge 007]

...в формуле нет ошибки?..

А Вы в файл, что я выкладывал, загляните. Есть там ошибка?

...формула подойдет и для А столбца?..

Попробуйте. Если подойдёт - выкладывайте рабочий файл