Почему не работает суммеслимн в excel
В версиях Excel 2007 и выше работает функция СУММЕСЛИМН, которая позволяет при нахождении суммы учитывать сразу несколько значений. В самом названии функции заложено ее назначение: сумм а данных, если совпадает мн ожество условий.
Синтаксис СУММЕСЛИМН и распространенные ошибки
Аргументы функции СУММЕСЛИМН:
- Диапазон ячеек для нахождения суммы. Обязательный аргумент, где указаны данные для суммирования.
- Диапазон ячеек для проверки условия 1. Обязательный аргумент, к которому применяется заданное условие поиска. Найденные в этом массиве данные суммируются в пределах диапазона для суммирования (первого аргумента).
- Условие 1. Обязательный аргумент, составляющий пару предыдущему. Критерий, по которому определяются ячейки для суммирования в диапазоне условия 1. Условие может иметь числовой формат, текстовый; «воспринимает» математические операторы. Например, 45; « , = и др.).
Примеры функции СУММЕСЛИМН в Excel
У нас есть таблица с данными об оказанных услугах клиентам из разных городов с номерами договоров.
Предположим, нам необходимо подсчитать количество услуг в определенном городе с учетом вида услуги.
Как использовать функцию СУММЕСЛИМН в Excel:
- Вызываем «Мастер функций». В категории «Математические» находим СУММЕСЛИМН. Можно поставить в ячейке знак «равно» и начать вводить название функции. Excel покажет список функций, которые имеют в названии такое начало. Выбираем необходимую двойным щелчком мыши или просто смещаем курсор стрелкой на клавиатуре вниз по списку и жмем клавишу TAB.
- В нашем примере диапазон суммирования – это диапазон ячеек с количеством оказанных услуг. В качестве первого аргумента выбираем столбец «Количество» (Е2:Е11). Название столбца не нужно включать.
- Первое условие, которое нужно соблюсти при нахождении суммы, – определенный город. Диапазон ячеек для проверки условия 1 – столбец с названиями городов (С2:С11). Условие 1 – это название города, для которого необходимо просуммировать услуги. Допустим, «Кемерово». Условие 1 – ссылка на ячейку с названием города (С3).
- Для учета вида услуг задаем второй диапазон условий – столбец «Услуга» (D2:D11). Условие 2 – это ссылка на определенную услугу. В частности, услугу 2 (D5).
- Вот так выглядит формула с двумя условиями для суммирования: =СУММЕСЛИМН(E2:E11;C2:C11;C3;D2:D11;D5).
Результат расчета – 68.
Гораздо удобнее для данного примера сделать выпадающий список для городов:
Теперь можно посмотреть, сколько услуг 2 оказано в том или ином городе (а не только в Кемерово). Формулу немного видоизменим: =СУММЕСЛИМН($E$2:$E$11;$C$2:$C$11;F$2;$D$2:$D$11;$D$5).
Все диапазоны для суммирования и проверки условий нужно закрепить (кнопка F4). Условие 1 – название города – ссылка на первую ячейку выпадающего списка. Ссылку на условие 2 тоже делаем постоянной. Для проверки из списка городов выберем «Кемерово»:
Не хочет она работать у меня.объясните пожалуйста начинающему:почему?в чем ошибка?
попытался сохранить в формате xls.вроде работает.на всякий случай прикладываю формат xlsx
[admin]Тема закрыта. Причина: Нарушение правил пп 2[/admin]
Не хочет она работать у меня.объясните пожалуйста начинающему:почему?в чем ошибка?
попытался сохранить в формате xls.вроде работает.на всякий случай прикладываю формат xlsx
[admin]Тема закрыта. Причина: Нарушение правил пп 2[/admin] китин
[admin]Тема закрыта. Причина: Нарушение правил пп 2[/admin] Автор - китин
Дата добавления - 22.03.2013 в 10:28
Работает она у Вас. Показывает правильный результат - 0 (т.к. двойных совпадений строк в C и D нет) Serge_007
Работает она у Вас. Показывает правильный результат - 0 (т.к. двойных совпадений строк в C и D нет) Автор - Serge_007
Дата добавления - 22.03.2013 в 10:33
ха.значит я неправильно понял сам смысл этой формулы.то есть она считает только тогда,когда удовлетворяются ОБА условия а не каждое по отдельности.и как мне тогда сделать,как в примере?
ха.значит я неправильно понял сам смысл этой формулы.то есть она считает только тогда,когда удовлетворяются ОБА условия а не каждое по отдельности.и как мне тогда сделать,как в примере? китин
ЗЫ У Вас в примере некоторые критерии написаны не так же (без пробела) как данные, соответственно они считаться не будут правильно
ЗЫ У Вас в примере некоторые критерии написаны не так же (без пробела) как данные, соответственно они считаться не будут правильно Serge_007
ЗЫ У Вас в примере некоторые критерии написаны не так же (без пробела) как данные, соответственно они считаться не будут правильно Автор - Serge_007
Дата добавления - 22.03.2013 в 11:18
Ув. профи! Подскажите пожалуйста, почему не работает формула СУММЕСЛИМН. Весь день голову ломаю, вроде все правильно, может я чего-то не вижу?
Ув. профи! Подскажите пожалуйста, почему не работает формула СУММЕСЛИМН. Весь день голову ломаю, вроде все правильно, может я чего-то не вижу? Ksuxa
Добрый день.
Помогите, пожалуйста, в приложенном файле прописать функцию в столбце M,
которая бы суммировала колонки D, F, H, J, L при условии что в столбцах C, E, G, I, K есть дата (не пустая ячейка)
и что эта дата меньше, чем ячейка I1 (в которой стоит сегодняшнее число).
Добрый день.
Помогите, пожалуйста, в приложенном файле прописать функцию в столбце M,
которая бы суммировала колонки D, F, H, J, L при условии что в столбцах C, E, G, I, K есть дата (не пустая ячейка)
и что эта дата меньше, чем ячейка I1 (в которой стоит сегодняшнее число). AnnaLaska
Не могу понять, почему формула СУММЕСЛИ считает неправильно данные, вроде форматы одинаковы, буквы тоже, но реально некоторые позиции пропускает, и в ручную все проверять долго и муторно. Почему не считает?
Прикладываю фаил - там отчет по продажи в розничных точках, интересует выделить продажи одной марки за весь год, а затем построить график наиболее продаваемых позиций. Так вот почему то не совпадают цифры , если сравнить продажи посчитанные суммировано за каждый месяц, и то, что по итогам дает сумма по формуле СУММЕСЛИ.
Пробовал иногда копировать наименование номенклатуры и вставлять в раздел, где формула и считает кол-во, и что странно - тогда она вычисляет эту позицию, но искать каждый раз эти недостающие позиции муторно и долго. Повторюсь, что форматы и буквы и номенклатуре правильны.
Заранее благодарю в помощи.
ps прокрутите фаил вниз, КОНСУЛ Б.Дача посчитан не правильно, в других двух точках еле-еле вручную нашел не посчитанные формулой позиции и скопировал номенклатуру из которой идет выборка в те места, которые почему-то не считались. В чем дело?
Не могу понять, почему формула СУММЕСЛИ считает неправильно данные, вроде форматы одинаковы, буквы тоже, но реально некоторые позиции пропускает, и в ручную все проверять долго и муторно. Почему не считает?
Прикладываю фаил - там отчет по продажи в розничных точках, интересует выделить продажи одной марки за весь год, а затем построить график наиболее продаваемых позиций. Так вот почему то не совпадают цифры , если сравнить продажи посчитанные суммировано за каждый месяц, и то, что по итогам дает сумма по формуле СУММЕСЛИ.
Пробовал иногда копировать наименование номенклатуры и вставлять в раздел, где формула и считает кол-во, и что странно - тогда она вычисляет эту позицию, но искать каждый раз эти недостающие позиции муторно и долго. Повторюсь, что форматы и буквы и номенклатуре правильны.
Заранее благодарю в помощи.
ps прокрутите фаил вниз, КОНСУЛ Б.Дача посчитан не правильно, в других двух точках еле-еле вручную нашел не посчитанные формулой позиции и скопировал номенклатуру из которой идет выборка в те места, которые почему-то не считались. В чем дело? Мишель79
Не могу понять, почему формула СУММЕСЛИ считает неправильно данные, вроде форматы одинаковы, буквы тоже, но реально некоторые позиции пропускает, и в ручную все проверять долго и муторно. Почему не считает?
Прикладываю фаил - там отчет по продажи в розничных точках, интересует выделить продажи одной марки за весь год, а затем построить график наиболее продаваемых позиций. Так вот почему то не совпадают цифры , если сравнить продажи посчитанные суммировано за каждый месяц, и то, что по итогам дает сумма по формуле СУММЕСЛИ.
Пробовал иногда копировать наименование номенклатуры и вставлять в раздел, где формула и считает кол-во, и что странно - тогда она вычисляет эту позицию, но искать каждый раз эти недостающие позиции муторно и долго. Повторюсь, что форматы и буквы и номенклатуре правильны.
Заранее благодарю в помощи.
ps прокрутите фаил вниз, КОНСУЛ Б.Дача посчитан не правильно, в других двух точках еле-еле вручную нашел не посчитанные формулой позиции и скопировал номенклатуру из которой идет выборка в те места, которые почему-то не считались. В чем дело? Автор - Мишель79
Дата добавления - 17.01.2014 в 13:49
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel Starter 2010 Еще. Меньше
Функция СУММЕСЛИМН — одна из математических и тригонометрических функций, которая суммирует все аргументы, удовлетворяющие нескольким условиям. Например, с помощью функции СУММЕСЛИМН можно найти число всех розничных продавцов, (1) проживающих в одном регионе, (2) чей доход превышает установленный уровень.
Это видео — часть учебного курса Усложненные функции ЕСЛИ.
Синтаксис
СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
=СУММЕСЛИМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Артем")
=СУММЕСЛИМН(A2:A9; B2:B9; "<>Бананы"; C2:C9; "Артем")
Имя аргумента
Диапазон_суммирования (обязательный аргумент)
Диапазон ячеек для суммирования.
Диапазон_условия1 (обязательный аргумент)
Диапазон, в котором проверяется Условие1.
Диапазон_условия1 и Условие1 составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента Диапазон_суммирования.
Условие1 (обязательный аргумент)
Условие, определяющее, какие ячейки суммируются в аргументе Диапазон_условия1. Например, условия могут вводится в следующем виде: 32, ">32", B4, "яблоки" или "32".
Диапазон_условия2, Условие2, … (необязательный аргумент)
Дополнительные диапазоны и условия для них. Можно ввести до 127 пар диапазонов и условий.
Примеры
Чтобы использовать эти примеры в Excel, выделите нужные данные в таблице, щелкните их правой кнопкой мыши и выберите команду Копировать. На новом листе щелкните правой кнопкой мыши ячейку A1 и в разделе Параметры вставки выберите команду Использовать форматы конечных ячеек.
Проданное количество
=СУММЕСЛИМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Артем")
Суммирует количество продуктов, названия которых начинаются с Я и которые были проданы продавцом Артем. Для поиска совпадающих названий продуктов в Criteria_range1 B2:B Criteria_range2 9 в группе Условия1(=A*) используется подп. Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 20.
=СУММЕСЛИМН(A2:A9; B2:B9; "<>Бананы"; C2:C9; "Артем")
Суммирует количество продуктов, которые не являются бананами и которые были проданы продавцом по имени Артем. Он исключает бананы, используя<>в критерии1, "<>Бананы"и ищет имя "Том" в Criteria_range2 C2:C9. Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 30.
Распространенные неполадки
Вместо ожидаемого результата отображается 0 (нуль).
Если выполняется поиск текстовых значений, например имени человека, убедитесь в том, что значения аргументов Условие1, 2 заключены в кавычки.
Неверный результат возвращается в том случае, если диапазон ячеек, заданный аргументом Диапазон_суммирования, содержит значение ИСТИНА или ЛОЖЬ.
Значения ИСТИНА и ЛОЖЬ в диапазоне ячеек, заданных аргументом Диапазон_суммирования, оцениваются по-разному, что может приводить к непредвиденным результатам при их суммировании.
Ячейки в аргументе Диапазон_суммирования, которым присвоено значение ИСТИНА, оцениваются как 1. Ячейки, которым присвоено значение ЛОЖЬ, оцениваются как 0 (ноль).
Рекомендации
Необходимые действия
Использование подстановочных знаков
Подстановочные знаки, такие как вопросительный знак (?) или звездочка (*), в аргументах Условие1, 2 можно использовать для поиска сходных, но не совпадающих значений.
Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед вопросительным знаком.
Например, формула =СУММЕСЛИМН(A2:A9; B2:B9; "=Я*"; C2:C9; "Арте?") будет суммировать все значения с именем, начинающимся на "Арте" и оканчивающимся любой буквой.
Различия между функциями СУММЕСЛИ и СУММЕСЛИМН
Порядок аргументов в функциях СУММЕСЛИ и СУММЕСЛИМН различается. Например, в функции СУММЕСЛИМН аргумент Диапазон_суммирования является первым, а в функции СУММЕСЛИ — третьим. Этот момент часто является источником проблем при использовании данных функций.
При копировании и изменении этих похожих формул нужно следить за правильным порядком аргументов.
Одинаковое количество строк и столбцов для аргументов, задающих диапазоны ячеек
Аргумент Диапазон_условия должен иметь то же количество строк и столбцов, что и аргумент Диапазон_суммирования.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
- Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Читайте также: