Задача такая, есть лист, в данном случае Лист1, в столбце A указанны счета, в столбце B указанны суммы (в реальной задаче столбцы стоят не рядом).
Нужно посчитать сумму по столбцу B привязываясь лишь к значению счетов, где счета соответствуют некоторому набору значений, счетов может быть несколько десятков.
Я эту задачу решаю подобными формулами
Проблема в том, что длина формул ограничена 255 знаками и если список счетов большой, то приходиться в нескольких отдельных ячейках делать подсчёт части сумм и в итоговой ячейке суммировать вычисленные суммы. Во первых это как-то не очень "красиво", во вторых даже если счетов не много, всё равно формула получается длинная и плохо читаемая. К тому-же, чтобы увидеть из каких счетов она складывается, надо заходить в саму формулу, что не очень удобно, когда таких формул много.
Можно ли как-то упростить вид формулы и сократить её длину.
В идеале бы подошли один из двух вариантов:
1. Во вспомогательной ячейке расположенной справа от основной, разместить список счетов разделенных между собой к примеру запятой, а в основной ячейке написать формулу делающую расчёт на основе списка из вспомогательной ячейки.
2. Используются вспомогательные ячейки в количестве счетов, по которым надо вычислить сумму.
К примеру они располагаются справа от основной формулы (т.е. к примеру, если основная формула расположена в ячейке A1 и надо найти сумму соответствующую трём счетам, то список этих счетов должен размещаться в ячейках B1, C1, D1). А основная формула должна как-то ссылаясь на списки счетов во вспомогательных ячейках, считала сумму по этим счетам.
При этом желательно, чтобы длина основной формулы особо не зависела от количества счетов и длины имени листов и вписывалась в заданные 255 знаков и была универсальна, в том смысле, чтобы её можно было растянуть вдоль столбца и лишь менять список счетов во вспомогательных ячейках по каждой строке.

Возможно есть какие-то другие идеи (вариант с сокращением длины листа с которого идёт расчёт до одного знака не рассматривается, т.к. названия листов менять нельзя, к тому-же всё равно при очень большом количестве счетов часть счетов всё равно не влезет).

Файлик для экспериментов в прилагаю:

vikttur и qwrqwr огромное спасибо, оба решения дают то что нужно и самое приятное, что формулы не ддлинные и визуально все видно, даже не ожидал.

Поизучаю справку с БДСУММ, ранее в своей практике ещё не проверял.
Вариант с формулой массива ещё повысил мои знания в этой области и способах его применения.
Ещё раз всем спасибо!

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

MitarЗдравствуйте!
Мысли вслух:
=СУММПРОИЗВ(НЕ(ЕОШ(НАЙТИ($A$1:$A$8;$C$9;1)))*1;$B$1:$B$8),
В ячейке C9 например через запятую указывается перечень используемых при подсчете счетов.Да, проверил, тоже работает. Кстати когда увидел и разобрался с вариантом vikttur -а, решил реализовать по аналогии формулу для одной ячейки со списком счетов перечисленных через запятую, собирался как раз сделать эта после обеде, а тут пришёл, а вариант уже готовый есть.
У меня правда мысль была немного другая, но тоже с использованием НАЙТИ. Если вдруг получиться свой вариант, то тоже напишу.

Что касается замечания qwrqwr про "АБС1,ЭЮЯ1", то эту ошибку легко обойти, достаточно использовать некий символ разделитель, который ни при каких обстоятельствах не встречается в названии счета.

Например, если использовать в качестве разделителя запятую " , ", то в ячейке со списком счетов должно быть что-то вида " , счет1 , счет2 , счет3 , " и соответственно при поиске искать счет окруженный разделителями, например " , счет1 , ". Это конечно не столь красиво, но главное работает.
Конкретно в моей ситуации счетов не более 20-30, поэтому 255 символов точно хватит. А если использовать постолбцовый вариант, то 255 столбцов тем более хватит.
А так в принципе в случаях, когда список счетов может быть очень огромным, там конечно уже придётся расписывать их вдоль строк, но к сожалению уже на отдельном листе, что будет не столь наглядно.

qwrqwrDjon Playerформула с БДСУММ выдает ошибку #ЗНАЧ, видимо там можно только вертикальный диапазон задавать. Да
Djon PlayerА можно как-то указать горизонтальный диапазон, но с помощью какой-то операции его в формуле транспонировать? Наверное можно только с дополнительным столбцом.
Типа того (в C4:C9 теперь формула):
(только мне так меньше нравится - так ведь не более 255 номеров счетов задать можно :)
Проблемка в то, что мне как раз надо вычислять не одну формулу, а много формул, грубо говоря есть столбец примерно из 15 строк вдоль которого расположены формулы считающие каждая по своему списку счетов и по этой вот причине использовать вертикально расположенные списки счетов не совсем удобно, если только не расположить их диагонально (только то пришла такая мысль в голову). Поэтому надо либо в одну ячейку поместить весь список счетов, либо вдоль строчки. Если-же вертикально размещать, то уже на отдельном листе (можно даже на текущем листе расположить их горизонтаьно, а на другом с помошью ТРАНСП как вы привели пример, вертикально, но это уже не столь удобно.

viktturОбъединять в ячейке, чтобы потом разъединять? Сизифы? :)Список счетов я формирую вручную, т.е. не копирую ниоткуда, поэтому мне без разницы в отдельные ячейки его заполнять или в одну ячейку, так что тут проблемы нет.

qwrqwr,
Это можно решить так:
=СУММПРОИЗВ(НЕ(ЕОШ(НАЙТИ("," & $A$1:$A$8 &","; $C$9;1)))*1;$B$1:$B$8)

в ячейке C9 должно быть ,qwe1,asd1,
Тогда будет 6.

Вобщем в итоге я использовал формулу Mitar -а, слегка модифицируемую, т.к. проще было переделывать сущестувющий набор формул вида СУММЕСЛИ путем автозамены так, чтобы оставались лишь счета.
В итоге у меня получилось примерно так:
В ячейке EC15 массив формул:
{=СУММПРОИЗВ(НЕ(ЕОШ(НАЙТИ("," & Доходы_ЕПЗИ!$C$9:$C$300 & ","; $ED15;1)))*1;Доходы_ЕПЗИ!$F$9:$F$300)}
В ячейке ED15 список счетов:
,И901035,И901036,И901037,И901038,И901040,И901041,И901042,И901043,И901044,И901150,
Таких строк у меня 15, сейчас как раз занимаюсь переделкой.

Всем спасибо! Другие варианты решения всегда приветствуются.

Наконец-то закончил переделку дурацкого отчета из-за которого я и создал эту тему.
До этого он мне каждый квартал доставлял массу неприятностей, т.к. каждый раз приходилось заново устанавливать формулы, брать из другого отчета за предыдущий период и вставлять в новый шаблон (его каждый раз присылает головная компания и он всё время модифицируется). Потом заниматься сверкой корректности формул в соответствии с полем в котором расписано словами описание расчетов, на это уходило кучу времени и нервов (из-за осознания бесполезности такой работы, всё равно что каждый три месяца с нуля писать одну и ту-же программу).
Теперь-же как переделал, проверка соответствия списка счетов тому, что написано в поле описания занимает небольшой время, в среднем 1 минуты на 1 формулу, так что грубо говоря не более 15 минут на все формулы.
Зато при этом ещё и польза будет, буду вспоминать каждые 3 месяца про формулы массивов.
Огромное спасибо!

Т.к. наконец появилось чуть свободного времени, стал разбираться в принципах работы приведенных здесь формул массивов.
В результате родил свою формулу для случая, когда список счетов находится в одной ячейке I14, он разделен запятыми в качестве разделителя (без пробелов) и в начале и в конце так-же запятая.


В прикрепленном файле написал все варианты формул написанных в этой теме: