Выборочные вычисления по одному или нескольким критериям

Постановка задачи

Имеем таблицу по продажам, например, следующего вида:

cond_sum1.png

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig). Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

cond_sum2.png

Жмем ОК и вводим ее аргументы:

cond_sum3.png

  • Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
  • Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и  ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий ?????. А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
  • Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

cond_sum4.png

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2="Копейка")*(B2="Григорьев")

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

cond_sum5.png

Способ 4. Волшебная формула массива

Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:

=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

cond_sum6.png

После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM), которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:

=БДСУММ(A1:D26;D1;F1:G2)

cond_sum7.png


Страницы: 1  2  
Let
06.05.2013 23:36:32
В случаях с условным суммированием всегда предпочитал функцию "СУММЕСЛИМН", даже если условие было всего одно.

Несколько раз задавался вопросом: для чего же тогда нужна функция "СУММЕСЛИ", если "СУММЕСЛИМН" изначально позволяет задавать от 1 до 127 условий и по определению выглядит привлекательнее "СУММЕСЛИ", которая поддерживая всего 1 условие?

С какой тогда целью разработчики оставили "СУММЕСЛИ" в Экселе?
Ответ на данный вопрос получил на практике, когда в очередной раз перечитывал справку.
Дело в том, что диапазон суммирования и диапазон условий в "СУММЕСЛИ" могут не совпадать. Они могут быть разными! Что очень может выручить в ситуации, когда суммируем диапазон по критерию, который берем из другого диапазона, отличного по числу ячеек.  

К сожалению, так сделать в СУММЕСЛИМН не получится. Там диапазоны условий должны совпадать по количеству ячеек с диапазоном суммирования :( И в этом дополнительное весомое преимущество "СУММЕСЛИ"! :)
09.05.2013 15:29:07
Про эту особенность СУММЕСЛИ знаю, но ни разу не встречал реальных ситуаций или проектов, где она пригодилась бы. А вот трудно распознаваемых ошибок из-за этого видел кучу, когда пользователи случайно задают диапазоны проверки и суммирования разного размера, а функция начинает молча вычислять неправильное значение.

В любом случае, спасибо за уточнение - важный момент.
29.05.2013 10:11:07
еще можно сказать что,
если и делать разные диапазоны в СУММЕСЛИ (к примеру на таблице выше),
то важно выбрать весь диапазон с условиями (Range) в таблице (т.е. высоту диапазона), данные которых мы хотим суммировать, а в качестве диапазона суммирования (Sum_range) достаточно выбрать первую ячейку в столбце или больше (обязательно выбирать начиная с первой ячейки).
PS. выяснил, проверяя ваши комменты.....
07.06.2019 09:32:34
Доброго дня Николай!! Не подскажете как вытащить допустим менеджера из таблицы если за 3 дня подряд продажи больше например 10000?или за 7 дней подряд  больше 200 000? или за 30 дней подряд 3 раза по 100 000
12.01.2015 20:07:45
Оставили хотя бы из соображений обратной совместимости.
10.02.2022 22:02:55
Спасибо, очень пригодилось уточнение.
10.05.2013 20:42:16
Подскажите пожалуйста, а как, например, ввести формулу, если мы хотим получить сумму заказов для Копейки, но от двух менеджеров, скажем Григорьева и Лапина.
Столкнулся с такой необходимостью просуммировать по двум критериям, находящимся в одном и том же диапазоне.
Заранее сапасибо!
10.05.2013 22:46:08
Александр, нужно просто просуммировать две функции СУММЕСЛИ, одна из которых считает "Копейку" от Григорьева, а другая - "Копейку" от Лапина.
11.02.2014 12:10:16
Николай, добрый день! Подскажите, пожалуйста, других вариантов решения нет?
У меня 20 условий в одном диапазоне и 5 в другом, формулы получаются очень длинными. Пишу макрос, но выдаются ошибки. В basic формулы разбиваются амперсандом, кавычками и другими символами. Пыталась вручную редактировать, все равно не получается. Да и в basic не сильна(((
Приходится формулы разбивать, делать предварительный расчет в свободных ячейках, и потом только суммировать полученные значения.
Честно говоря, что только я уже не перепробовала, не получается. Может есть какой-то выход?))
12.02.2015 15:07:33
Николай, специально зарегистрировался на планете, чтобы задать вопрос. Я столкнулся как раз с такой ситуацией, когда мне нужно просуммировать ячейки по условию, содержащему, либо одно, либо другое слово, но и то и другое слово могут встречаться в ячейке одновременно. Разумеется, каждая СУММЕСЛИ суммирует у себя одну и ту же ячейку, которая содержит оба условия одновременно и итоговая сумма получается выше ожидаемой. Как поступить, если мне нужно просуммировать такие ячейки?

Кустарный способ, который я нашел - это отфильтровать столбец по СОДЕРЖ "*слово1* ИЛИ "*слово2*", а потом суммировать с зажатым CTRL, но что делать, когда таблица из 10000 строк.
12.01.2016 18:22:58
Максим, лучше поздно, чем никогда. По крайней мере я буду спокоен, чем смог помог!))
Вот вам тема к прочтению ТЫЦ
14.05.2013 10:54:37
Здравствуйте

Скажите пожалуйста, а как можно вместо диапазона внести имя присвоенное данному диапазону...или же проблема в том что диапазон постоянно расширяется и нужно чтобы диапазон формулы тоже расширялся.
17.05.2013 15:37:56
Вам нужны "умные таблицы" - они автоматически подстраиваются под размеры области данных см. тут
16.05.2013 18:47:55
Здравствуйте,

Николай, подскажите, пожалуйста. Я уже давно пользуюсь суммированием через =СУММПРОИЗВ((.....=..)*(.....=..)).
Такая формула вводится обычно - не как формула массива, если использовать просто СУММ. Чем различаются эти два способа суммирования по условию??? Может из них какой-то существенно проще для "железа"??? Заранее спасибо за помощь.
17.05.2013 15:37:04
Формулы массива обычно медленнее, т.к. пересчитывается весь массив, а не одна зависимая ячейка.
24.06.2013 13:17:45
Добрый день! Подскажите как при использовании функцией "СУММЕСЛИСЛИМ" одни из критериев сделать диапазон дат. Например с 1 января 2013 года по 1 февраля 2013 года. При этом нужно, чтобы границы диапазона были не прописаны в ручную в функцию (это не сложно), а брались из ячеек с данными для суммирования. Т.е. в приведенном примере добавляем еще один столбец с датой и две ячейки с датами обозначающими начало и конец коридора. Это нужно когда формула большая, а для вычисления постоянно нужно менять диапазон дат и, следовательно, прописывать их постоянно в ручную долго и не удобно. Заранее спасибо!
01.07.2013 21:20:01
Прописываете, предположим в ячейки А1 и А2 начальную и финишную даты. В аргументах СУММЕСЛИМН тогда будет:
диапазон суммирования - столбец с деньгами (или что вы там складываете)
диапазон условия 1 - столбец с датой
условие 1 - ">="&A1
диапазон условия 2 - столбец с датой
условие 2 - "<="&A2
28.03.2014 20:32:04
Доброго времени суток Всем! У меня вопрос связанный с данной темой следующий: Как просуммировать значения удовлетворяющие определенному критерию по суткам? Например: даты изначально есть в таблице и нужно суммировать минуты за сутки и если они превышают определенный лимит, то этот лимит обнуляется и счет начинается заново. И такая операция по каждым суткам, т.е. функция должна автоматически определять по каждой сутке. Спасибо!  

диапазон суммирования - столбец с минутами
диапазон условия 1 - столбец с датой и еще 2 критерии
условие 1 - "<="120 то =0
диапазон условия 2 - столбец с датой и еще 2 критерии
условие 2 - ">"120 то сумма по условию

Примерно такая ситуация
29.03.2014 17:46:52
Во-первых, без файла примера в такой задаче никто ничего путного вам сказать не сможет.
Во-вторых, я бы просто считал суммарное количество минут за сутки и потом с помощью функции ОСТАТ вычислял остаток от деления этого количества на лимит (для обнуления).
29.03.2014 20:59:54
Это мне как раз и нужно... Посчитать суммарное количество минут за сутки! Просто формулой какой пользоваться пока не знаю
28.09.2016 11:41:33
Николай, добрый день!
А как, например, так сделать, чтобы функция заносила последнее значение по определенному продукту на определенную (актуальную) дату.
То есть:
Столбец "А" - код продукта
Столбец "E" - дата
Столбец "F" - количество на начало периода (сюда должно попадать последнее значение по определенному виду продукта из столбца "I" )
Столбец "I" - количество на конец периода.
Продукта много, дат тоже....Сижу, ломаю голову уже не один день, но пока найти решение не могу, вот и решила обратиться...
Буду очень признательна за помощь! Спасибо..
11.07.2013 04:00:43
Здравствуйте, Николай. Очень хорошие статьи, много полезного для себя почерпнул, но вот пока не нашел одной функции, похожей на СУММЕСЛИ, но только для текстовых полей.
Какой функцией можно реализовать суммирование текстовых значений столбца в одну ячейку, так, чтобы значение (текст) каждой ячейки начинался с новой строки? Пробовал это реализовать функцие СЦЕПКА, но как-то не вышло =)
Подробнее. Есть столбец (A2:A100), принадлежащий таблице A2:K100. Начиная с A5 и заканчивая A100 редко попадаются текстовые значения. При этом в одной ячейке может быть два текстовых значения. Второе значение начинается с новой строки (alt+enter). Необходимо, чтобы в ячейку A1 выписались все текстовые значения встреченные в столбце. Каждое значение - с новой строки. Если это возможно только с помощью макроса, я был бы вам крайне признателен, хотя бы намекнуть в какую сторону двигаться в плане алгоритма. Благодарю заранее.
22.08.2013 21:56:50
Всем здравствуйте!
У меня возникла следующая проблема: необходимо просуммировать определенные значения в заданном диапазоне, сам диапазон - это некие различные числовые значения, соответствующие каждой дате с 1 января по 31 декабря. Получаем следующую картину: 1-й столбец - это даты, 2-й столбец - это соответствующий дате номер недели в году, 3-й столбец - это различные значения. Так вот, необходимо просуммировать первое и последнее значение в каждой неделе по всему году. Помогите, пжл, кто может...
28.08.2013 08:53:27
С такими вопросами лучше на  Форум (и файл обязательно приложить).
26.09.2013 14:01:37
Способ 4. Волшебная формула массива

я чтобы каждый раз не возиться с формулой сделал ссылки на две ячейки E1 и F1- очень удобно
{=СУММ((A1:A10=E1)*(B1:B10=F1)*C1:C10)}
Всех приветствую.
Подскажите, что я неправильно делаю когда хочу просуммировать по нескольким критериям и в одном условии идет подсчет ячеек с вхождением отдельных символов.
Использую формулу массива =СУММ(ЕСЛИ((.....=..)*(.....="*"&"искомые символы"&"*")).
Но она не работает если условий не одно, а несколько.
22.10.2013 01:05:37
По такой формуле (с многоточиями) я вам помочь не смогу. Нужен файл (который тут не прикрепить). Советую копать в сторону функции СУММЕСЛИМН - она удобнее при большом количестве условий.
27.10.2013 14:13:08
День добрый.
Подскажите как решить задачку. В таблице два столбца. Первый "А" - числовые значения, второй "В" -  текст (может быть одно слово, а может быть и 100 в одной ячейке). Нужно прописать формулу, чтобы она искала во втором столбце "В" совпадение текста (может быть и часть слова, как аналог возможности в автофильтре значение "содержит"), и в результате выдавала сумму значений ячеек столбца "А", когда она будет находить нужное значение в столбце "В".
Надеюсь понятно объяснил.
28.10.2013 09:25:46
Алексей, перечитайте внимательно Способ 1 (там, где про маски). Вам нужна формула вида:
=СУММЕСЛИ(B:B;"*ваштекст*";A:A)
vvl
03.04.2014 09:43:33
Алексей Б, это же можно посчитать с помощью массивов:
{=SUM((SUBSTITUTE(B:B;"часть слова";"")<>B:B)*IF(ISNUMBER(A:A);A:A;0))}
12.11.2013 12:27:20
Спасибо за статью несколько часов ломал голову, как просуммировать столбики по нескольким условиям, причем надо было выбрать чтобы суммировались лишь те, где напротив был текст, а не даты и числа, результат:
{=СУММ(ЕТЕКСТ(A1:A4)*B1:B4)}
11.01.2014 02:23:32
Скажите, пожалуйства, а есть ли какая-нибудь формула для суммирования по нескольким критериям, которая понимает знаки ">" и  "<"? Например, если бы в Вашем примере нужно было посчитать сумму продаж Григорьева стоимостью > 5000
11.01.2014 09:07:04
Екатерина, все функции СУММЕСЛИМН, СЧЕТЕСЛИМН, СРЗНАЧЕСЛИМН понимают эти знаки. Сумма продаж Григорьева >5000 выглядела бы как:
=СУММЕСЛИМН(D2:D26; B2:B26; "Григорьев"; D2:D26; ">5000")
25.06.2014 23:06:19
а если нужно посчитать сумму продаж Григорьева стоимостью > 5000 но <10 000 и указывать даный диапазон ссылаясь на ячейки, а не на сталые даные например  ячейка E1 будет содержать даные нижнего диапазона (5000) а ячека E2 верхнего диапазона (10 000) ???
28.03.2014 19:34:52
Добрый день.
Подскажите пож-та, какой формулой нужно воспользоваться, если необходимо сложить числа (C2-C32) по 3 показателям из одного диапазона(B2-B32)...нужно это сделать одной формулой. Заранее благодарю.
28.03.2014 20:45:34
=БДСУММ(С2:В32;С1;"1 показатель":"2 показатель":"3 показатель" )

Определите ячейки по вашим показателям))
Примерно так, кажется...
30.03.2014 19:36:55
Использовал СУММЕСЛИМН и СУММПРОИЗВ для подсчета сумм затрат на расходные материалы, которые группируются по услугам (15 услуг) по предприятиям (около 20 предпр.), при навигации, переходе между листами и ячейками, сильно снизилось быстродействие Excel, если, например, переходить   с ячейки на ячейку - такое чувство, что эти формулы пересчитываются заново, пришлось отказаться от такого решения. Действительно ли эти формулы, если их много (у меня получилось их 15*20 = 300),  могут повлиять на быстродействие?
31.03.2014 07:43:34
Игорь, надо смотреть на файл и ваш компьютер. Но, по идее, не должны. 300 ячеек с формулами - это ерунда для любого Excel (даже если это формулы массива).
v.plagov
02.04.2014 00:06:25
Наконец-то я увидел способ, как делать суммирование в рамках определенной даты. Для таблицы личного бюджета - полезный прием.
Спасибо.
19.04.2014 10:59:13
Я личные финансы с помощью сводной таблицы анализирую - получается быстрее и проще, чем СУММЕСЛИ. Но это дело вкуса, конечно.
26.05.2014 18:15:56
Николай, а ведь пример с СУММПРОИЗВ был раньше здесь размещен?
19.06.2014 10:14:23
Подскажите.
Если требуется подсчитать количество непустых ячеек в столбце Сумма, с условием по полю Заказчик. Как это просто сделать? Сломал голову )

СчетЕсли вроде бы не дает возможности накладывать условие на другой столбец.

Заранее спасибо!
15.10.2014 22:19:38
Если столбец А - наименование заказчиков, столбец B - суммы, то
Первый вариант:
=СЧЁТЕСЛИМН(A2:A7; "заказчик"; B2:B7; ">0")
Вариант 2:

=СУММПРОИЗВ(((A2:A7)="заказчик")*((B2:B7)>0)*1)
02.09.2014 15:06:01
Подскажите как рассчитать время работы кассы, есть № чека время продаж каждой позиции в чеке. Необходимо по №-ру чека определить время потраченное на продажу (от максимального отнять минимальное), проблема в том что чеков более 1000 хочется упростить.
Спасибо.
КассаНомер
чека
Время
14807468:27:00
14807468:27:00
14807468:27:01
14807468:27:02
14807468:27:03
14807468:27:06
14807468:27:06
14807468:27:07
14807468:27:08
14807468:27:10
14807478:27:00
14807478:27:02
14807478:27:03
14807478:27:10
14807478:27:13
14807478:27:16
15.10.2014 22:30:00
Если в столбце B - номер чека, в столбце C - время операции, то задачу можно решить следующей формулой:

максимум:
=ЕСЛИ((B3:B18)=480746; МАКС(C3:C18)) 

минимум:
=ЕСЛИ((B3:B18)=480746; МИН(C3:C18))

соответственно время работы кассы:
=ЕСЛИ((B3:B18)=480746; МАКС(C3:C18)) - ЕСЛИ((B3:B18)=480746; МИН(C3:C18))
*похоже промахнулся веткой, если есть модерация, прошу исправить, сообщение относится к посту #3886
15.10.2014 22:42:32
Я бы также хотел поинтересоваться по поводу решения одной задачи.
Есть столбец, в котором содержатся данные различного формата (в основном числовые, но также есть текстовые записи). Необходимо посчитать сумму абсолютных значений по определенным ячейкам данного столбца. В моем случае, я решил использовать управляющий столбец, в котором содержится запись "%", если значение в данной строке необходимо учитывать в расчете суммы абсолютных значений. Пример этих двух столбцов:
Столбец А Столбец B
Значение40
%10%
текст, который все портит
Значение300
Отклонение-15
%-5%
Для решения вопроса суммирования абсолютных значений были испробованы различные варианты, которые прекрасно работают, если в ячейках столбца суммирования нет текстовых значений. В случае, когда в нем есть текстовые строки, все решения выдают результат "#ЗНАЧ!", не смотря на то, что данные строки не являются элементом реального суммирования.

Приведу в пример формулу, результат которой возвращает данное значение:

=СУММ(((A2:A11)="%")*(B2:B11)*ЗНАК(B2:B11)) 
Вопрос такой, можно ли как-то научить эту формулу не пытаться суммировать значения, где есть текст, если я уверен, что строки, в которых по управляющему столбцу стоит знак "%" содержат только числовые значения?..
11.11.2014 10:24:25
В вашу формулу надо добавить проверку ячейки на содержание в ней текста с помощью функции Т. Что-то вроде:
=СУММ(((A2:A11)="%";)*(B2:B11)*ЗНАК(B2:B11)*НЕ(T(B2:B11)))
Не видя файла точнее сказать не смогу, но рыть надо в этом направлении.
11.11.2014 13:39:17
Спасибо за ответ, удалось реализовать эту формулу следующим образом, добавив предварительную проверку на то, что ячейка является числом:

=СУММ(ЕСЛИ(($A2:$A159)="%";ЕСЛИ(ЕЧИСЛО(B2:B159); B2:B159*ЗНАК(B2:B159))))

11.11.2014 09:49:09
Приветствую!

Прошу помощи.
Нужно отбор производить по наличию числа в сумме, например:

в столбце А указаны 9-значные числа (балансовые счета)
нужно поэтому столбцу сделать отбор строк
по ячейкам содержащим например число 1411
чтоб просуммировать значения в другом столбце (суммы)
я пробовал через критерий *1411"
но как я понял
критерий на содержание по числам не работает
только текст

подскажите пожалуйста, как по числам ставить критерий на содержание числа (однозначного и выше) в другом числе со значностью выше.
например
20 в числе 1220698
11.11.2014 10:19:50
Сначала надо в отдельном столбце преобразовать ваши номера счетов в текст с помощью функции вида =ТЕКСТ(A1;"@"), а потом уже применять функции СЧЁТЕСЛИ и СУММЕСЛИ к этому текстовому столбцу. И все получится :)
14.01.2015 14:50:26
=ТЕКСТ(A1;"@"
при применении к номеру счета со значением - 141240000592
в ячейке показало  - 1,4124E+11
как избежать такое представление?
в этом представлении условие выбора не сработало.
про символ @ в excel - в инетернете ничего не смог найти.
07.02.2015 16:56:45
Если вы видите 1.412Е+11 - значит у вас преобразование числа в текст не сработало (это экспоненциальный формат для больших чисел).
Соответственно, и функции СЧЁТЕСЛИ и СУММЕСЛИ потом тоже сработать нормально не могут.
Не видя вашего файла, не смогу сказать - почему.
11.11.2014 10:51:50
Да. сработало, спасибо.
20.11.2014 07:43:33
Здравствуйте, подскажите пожалуйста, почему значение получается 0. Может зависеть решение от того, что диапазон суммирующих ячеек находится на одном листе, а критерии на другом.?=СУММЕСЛИМН(зак!H$2:H$30000;зак!D$2:D$30000;[@№];зак!D$2:D$30000;[@№тел1];зак!D$2:D$30000;[@№тел2];зак!D$2:D$30000;[@№тел]).
07.02.2015 16:32:37
Ирина, это влиять, по идее, не должно - ищите причину в другом.
Добрый день! Подскажите пожалуйста, есть несколько столбцов с данными:
№п/п№ машиныВремя№п/п№ машиныВремя№п/п№машиныВремя№машиныВремя
191
90180
Нужно посчитать сколько рейсов делала машина с определенным номером в отдельной таблице, но диапазон должен браться из столбцов с номером машины. Блин криво как-то объяснил :) Но суть вроде понятна
07.02.2015 16:32:06
Не понятна, если честно. Лучше создайте тему на форуме и приложите файл с примером :)
05.01.2015 07:56:25
Подскажите пожалуйста как подсчитать примерно тоже самое всё (для какого-нибудь Петрова), но с разных листов (листы "1", "2", ..., "31";) на отдельный лист "Месяц", например. Очень нужно.
Условие только одно - значение напротив фамилии работника. Эти значения на всех листах надо "пофамильно" просуммировать.
07.02.2015 16:30:13
Дмитрий, посмотрите статью по консолидации - думаю, она вам больше подходит.
07.05.2015 11:28:40
Николай, не нашел подсказки в статье по консолидации (либо не совсем понял как применить). Как быть когда надо сделать следующее: =СУММЕСЛИ('01:31'!C6;">0";)или =СЧЁТЕСЛИ('01:31'!C6;">0";) или =СРЗНАЧесли('01:31'!C6;">0";)

Формула выдает ошибку (#ЗНАЧ!)

Формулы без условий (если) - работают хорошо.
Но мне надо высчитать среднее значение по одной ячейки в разных листах, нули при этом брать в расчет не нужно (тогда выйдет не верный результат вычисления среднего значения).

Можете что-то подсказать?
24.07.2015 00:37:46
Скажите, пожалуйста, как использовать не весь диапазон таблицы, а только отфильтрованный????
02.10.2015 10:13:20
Добавить еще условий в СУММЕСЛИМН, чтобы воспроизвести предварительный отбор фильтром.
20.08.2015 11:11:44
Здравствуйте, Николай,

Второй день не могу найти решение: требуется суммирование по нескольким условиям, но одно из условий не простое: если ячейка (строка) содержится в другой строке. Различные варианты CSE функций ПОИСК, ПОИСКПОЗ не "прошли", ошибки выдавали, пример с комментариями по ссылке. Можно решить?
Применение: из отчета по продажам, выгруженном из 1С, получить сумму продаж по наименованию, если оно содержится в списке, запихнутом в 1 ячейку в качестве строки.

пример
01.10.2015 13:00:29
Здравствуйте! Подскажите пожалуйста, не знаю как задать условие. Ситуация допустим как в Вашем примере - нужно просуммировать все продажи, которые реализовал менеджер Григорьев, но по всем магазинам копейка, копейка № 1, копейка № 2 и т.д., в общем по всем магазинам копейка не зависимо от номера. Как это сделать? спасибо.
02.10.2015 10:11:06
Напишите в условии *копейка* - и будет искать любые магазины. Звездочка - это символ подстановки, подразумевает любое количество любых символов. Посмотрите видео - там Ашаны так же считаются.
02.10.2015 10:48:59
Большое спасибо!
02.10.2015 13:17:15
Подскажите пожалуйста, не получаеться привязать условие к ячейке. Формула вида: =СУММЕСЛИМН(D19:5000;E19:E5000;Лист2!$Q$1;A19:A5000;">=Лист2!G18" ) где  Лист2!G18 - ячейка с датой, выбираемой из списка. Именно к ней надо привязать формулу. но итог всегда = 0. Если вместо Лист2!G18 указываю например "27.09.2015" так щитает.

Вот формула из примера  =СУММЕСЛИМН(D: D;E:E;">31.1.2014";E:E;"<1.03.2014" ) так работает, но если в качестве условия задать ячейки : =СУММЕСЛИМН(D: D;E:E;">L1";E:E;"<L2" ) - дает 0.
08.10.2015 11:54:25
Ой.. извиняюсь, надо было внимательней читать коментарии. Нашла ответ сама
17.10.2015 02:50:08
Подскажите пож-та как использовать ссылку на ячейку, в которой заложен набор символов, с которых должен начинаться поиск значений. Т.е. к примеру условие "копейка*" не подходит . Нужно чтобы в условии использовалась ячейка , а не константа
06.12.2015 14:30:58
Если в ячейке А1 у вас нужный текст, то в условии можете написать A1&"*", т.е. к содержимому ячейки приклеить звездочку, используя символ склейки (&)
02.11.2015 09:34:22
=СУММЕСЛИ(Луч17!E5:E44+Луч17!E57:E96;"Датчик";Луч17!F5:F44+Луч17!F57:F96) Формула выдает ошибку! Как подсчитать несколько диапазонов на одном и нескольких листах?
10.11.2015 16:08:24
Добрый день. А можно сделать диапазон суммирования динамическим. У меня данные расположены в разных столбцах по месяцам. Как сделать так, чтобы при выборе месяца диапазон прыгал на нужный столбец?
06.12.2015 14:29:47
Копайте в сторону функций ДВССЫЛ (INDIRECT) или СМЕЩ (OFFSET)
05.12.2015 17:45:18
Добрый день! А можно ли както в функции суммеслимн одним из условий сделать цвет заливки ячейки? к примеру, первый диапазон это имена, соответственно условие 1 это имя, диапазон 2 это даты (раскрашенные разными цветами), и нужно както выбрать 2м условием диапазон дат. Читал ниже что можно провернуть это както так:
диапазон условия 1 - столбец с датой
условие 1 - ">="01.01.15
диапазон условия 2 - столбец с датой
условие 2 - "<="&07.01.15

но таким способом нужно подсчитать много ячеек, а каждый раз прописывать в формуле руками даты, или создавать дополнительно ячейки с датами не совсем удобно да и долго, вот собственно и интересно, можно ли условием сделать цвет
08.01.2016 11:42:14
Добрый день!

Прошу помочь с формулой для суммирования.
условия следующие: на одном листе столбцы, которые будут заполняться по мере оформления заказа
номер заказазакуп
20898250
20899820
208991984
20899975
209001618
2090194
20902512
20902315
20902267
на другом листе нужно чтобы суммы складывались
номер заказазакуп
20898
20899
20900
20901
я предполагаю, что условием должно быть если цифра в первом столбце на первом листе равна цифре в первом стобце на втором листе, то числа второго столбца складываются, прошу помощи в реализации
17.03.2016 01:08:34
Здравствуйте Николай.

Спасибо за прекрасный сайт, очень много полезного, реально помагает :)
Подскажите пожалуйста, как можно заменить функции СУММЕСЛИ и СУММЕСЛИМН на АГРЕГАТ или что-то другое, чтобы при выборке из умной таблицы и фильтрации по ней не учитывались данные из скрытых строк? с СУММЕСЛИ все работает, но вот при применении фильтра по некоторым параметрам - результат выборки не меняется, т.к. эти функции считают все подряд, видимое и скрытое... Спасибо
12.09.2016 21:34:09
Функция АГРЕГАТ умеет различать скрытые и отфильтрованные строки - должна подойти.
13.05.2016 20:24:41
Здравствуйте, Николай Павлов.
Хочу вас поблагодарить, за прекрасный сайт с подробными комментариями и разъяснениями, очень выручили.
Ради этого и зарегистрировался.
Большое спасибо!!!
12.09.2016 21:32:41
Спасибо!
01.08.2016 21:01:16
Добрый вечер! Очень нужна Ваша помощь: Проблема заключается в следующем Напротив фамилии предпринимателя есть жёлтая ячейка.В неё надо суммировать все значения под ней (снизу),но только до следующей жёлтой ячейки.Проблема в том ,что суммированых чисел может быть и три и четыре и пять и т.д . Стандарт здесь не подходит.Ставить формулы в каждую жёлтую ячейку да ещё и разные - это не вариант, так как строчек очень много. Пробовала через ЕСЛИ, но что-то не очень получается. Буду благодарна за помощь
12.09.2016 21:33:21
С цветом обычные формулы работать не умеют. Так что такое только программированием макросов через VBA.
08.09.2016 00:35:18
Похоже тему забросили...
Но все же - может кто подскажет..

Если задачу изменить:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".

на:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазинов "Копейка" и "Метро"

???
12.09.2016 21:32:25
Считаете все заказы Григорьева для Копейки.
Считаете все заказы Григорьева для Метро.
Складываете два числа :)
Добрый день!

Извините, если пишу не в ту тему, а вопрос следующий!
Есть база данных (наименование/группа/подгруппа/продажи).
Необходимо сделать отдельную таблицу, в которой будет сумма продаж в разрезе каждой подгруппы, только не вся, а 80% от всей суммы.
(этакий АВС анализ скажем так). И далее в другом столбце необходимо количество наименований, сумма которых составляет эти 80%.
Николай, спасибо за совет!

Только вот мне это необходимо сделать в разрезе каждой подгруппы, а не просто списка наименований.
Если я все правильно поняла, то со сводной таблицей придется делать много ручных манипуляций.  
Здравствуйте! Очень нужна помощь.
Возможно ли с помощью суммеслимн суммировать диапазон, одно из условий = за конкретный месяц.
допустим:
Условие 1=Январь (столбец дата в формате 03.01.2016)
Условие 2=Петров (столбец Ф.И.О.)
Условие 3 =ГСМ (столбец статья расхода)

Вот эта формула считает за Январь: =СУММПРОИЗВ(--(МЕСЯЦ(В1:В3)=1);С1:С3). А надо применить ещё условия отбора. Вставляю в суммеслимн (МЕСЯЦ(В1:В3)=1) как условие - не работает.
15.11.2016 19:32:17
Добрый день! Спасибо за форум!
У меня вопрос, как суммировать диапазон, исключив ячейки с ошибкой?
У меня массив постоянно обновляющихся данных компаний по разным секторам, и если я хочу суммировать данные по одному сектору я должна сначала обозначить диапазон отбора сектора, установить критерий (определить нужный сектор), потом задать диапазон суммирования, и вот на этом этапе исключить ошибки (некоторые компании данные не предоставляют)
Я так поняла это будет SUMIFS(диапазон, критерий1, диапазон суммирования, И ВОТ ТУТ его надо исключить ошибку! )
Либо  =AGGREGATE(9 (это функция суммирования, 6(исключение ошибки, диапазон)  - но в этом варианте возникает вопрос как отобрать по сектору предварительно?

Спасибо!  
04.02.2017 18:46:37
Добрый день, прошу проконсультировать по следующей теме :
есть столбец ячеек, в самом низу которого Стоит ячейка с формулой суммирования ячеек этого столбца (12 строк по числу месяцев в году),
в каждой из строк слева от этого столбца производится ряд вычислений и изначально в крайнем столбце выводится некое значение вычислений с использованием в расчётах некой константы.Это сильно загружало таблицу и она была труда в восприятии - путём составления условий (при которых цвет чернил совпадает с цветом фона) я убрал лишнюю ВИДИМУЮ загрузку в каждой из ячеек столбца, но сумма всех ячеек столбца постоянно показывает сумму всех ячеек (12 ячеек с учётом некой константы, используемой в расчётах)
Суть вопроса- как сделать так, что бы в ячейке с формулой суммы этого столбца выводился результат суммирования только тех ячеек,  в строках которых появилась хотя бы одна ячейка с внесёнными данными ?
в упрощённом виде имеем 12 строк ( по числу месяцев) в три столбца : приход + некая постоянная величина, расход, остаток - в ячейке сумма остатков надо иметь только факт по текущему месяцу! А остальные, не заполненные строки учитываться не должны.
Вот ссылка на таблицу https://yadi.sk/d/qojfWrhx3Cqpf3

Я говорю о ячейке H15 и в данном случая должно быть значения , как в январе - 8 999,00
Спасибо.
13.02.2017 21:10:50
Добрый день. Подскажите пожалуйста кто знает. У меня ситуация несколько иная, а именно, ячейки с критериями и суммируемые ячейки находятся в одной строке. Каждая строка состоит из 5-ти этапов (оплата товара, привоз, поиск покупателя, демонстрация покупателю, продажа), каждый этап оплачивается отдельно и каждый этап может выполнять разный исполнитель. Исполнитель на каждом этапе выбирается из списка. Выглядит так:
A1-исполнитель (покупка), B1-З/П (руб.), C1-З/П (долл.), D1-исполнитель (привоз), E1-З/П (руб.), F1-З/П (долл.), G1-исполнитель (поиск покупателя), H1-З/П (руб.), I1-З/П (долл.) и т.д.
Нужно просуммировать зар.платы в рублях и в долларах по каждому исполнителю.
Как это можно сделать?

P.S. Сейчас формула выглядит так: ЕСЛИ($M15="Руслан";N15;0)+ЕСЛИ($P15="Руслан";Q15;0)+ЕСЛИ($S15="Руслан";T15;0)+ЕСЛИ($V15="Руслан";W15;0). А это всего лишь часть формулы, там ещё есть проверка на "дурака".
17.02.2017 14:05:00
Добрый день.
Подскажите а как правильнее прописать формулу, если в способе № 4 будет не одно значение, а несколько в критерии F1:G4 и функцию прописывать с столбце H:
1. Копейка Григорьев -  =БДСУММ(A1:26;D1;F1:G2)
2. Рамстор Петров -    =БДСУММ(A1:26;D1;F1:G3)-H2
3. Ашан Чадов -           =БДСУММ(A1:26;D1;F1:G4)-H2-H3
11.03.2017 19:01:08
Здравствуйте! У меня такой вопрос есть по данной теме: Я планирую Бюджет Автозапчастей в инвестиционном проекте длительностью 10 лет. В частности в данном Бюджете я рассчитываю месяц в котором будет происходить замена шин на машине. Формулу расчета я привожу ниже:

=ЕСЛИ((СУММ($G$14:G$14)-СУММЕСЛИ($G$3:G$3;"*квартал";$G$14:G$14)-СУММЕСЛИ($G$3:G$3;"*месяцев";$G$14:G$14))>G$10*(СУММ($F$16:F$16)+1);1;0)

В строках $G$3:G$3 указана периодичность года (месяц, квартал, 6 месяцев, 9 месяцев, 12 месяцев), в строках $G$14:G$14 указан плановый пробег автомобиля, в строках G$10 указана эксплуатационная норма пробега шины, в строках $F$16:F$16 показывается коэффициент выполнения замены шин. Если суммарный пробег больше эксплуатационной нормы, то коэффициент равен 1, в противном случае 0.

Но не знаю как сделать так чтобы применяя формулу на следующем листе она учитывала произведенные замены шин из таблицы предыдущего листа? Таких листов у меня 10.
16.03.2017 18:39:19
Добрый день!
Подскажите пожалуйста, можно ли без макроса, просто формулой подсчитать сумму красного цвета цифр в строке таблицы
именно не отрицательных, а просто красного цвета.
Этим цветом отмечены ночные смены в графике отработанного времени.
Страницы: 1  2  
Наверх