Функция СУММЕСЛИМН используется для суммирования переданных значений с учетом нескольких критериев отбора данных, которые указываются в качестве аргументов функции, и возвращает полученное суммарное значение.
Примеры использования функции СУММЕСЛИМН в Excel
В отличие от СУММЕСЛИ, в рассматриваемой функции предусмотрена возможность внесения до 127 критериев отбора данных для суммирования. СУММЕСЛИМН удобна для работы с объемными таблицами, содержащими большое число полей данных. Например, если требуется рассчитать общую сумму средств, полученных в результате поставки определенного типа товара указанной компанией в определенную страну, удобно использовать функцию СУММЕСЛИМН.
Динамический диапазон суммирования по условию в Excel
Пример 1. В таблице содержатся данные о успеваемости студентов по некоторому предмету в университете. Определить итоговую оценку для студентов с фамилией, начинающейся на букву «А» при условии, что минимальный балл должен быть не менее 5 (успеваемость студентов оценивается по 10-бальной шкале).
Вид таблицы данных:
Для расчета суммарного балла согласно установленным критериям используем следующую формулу:
Описание аргументов:
- C3:C14 – диапазон ячеек с баллами, из которых будут автоматически выбраны значения для расчета суммы, которые соответствуют установленным критериям;
- C3:C14 – первый диапазон ячеек, к которому будет применен первый критерий;
- «>5» – первое условие отбора значений из указанного выше диапазона;
- B3:B14 – второй диапазон ячеек (с фамилиями студентов), к которому будет применен второй критерий;
- «А*» – второе условие отбора значений (все фамилии, которые начинаются с буквы «А»).
Результат расчетов:
В результате формула автоматически определила динамически изменяемый диапазон в соответствии с условиями отбора значений и просуммировала его.
Выборочное суммирование по условию в Excel
Пример 2. В таблице указаны данные об отгрузках трех товаров в различные страны на протяжении трех месяцев. Определить суммарный доход от отгрузок товара №2 в Казахстан за июнь и август.
Вид таблицы данных:
Для расчета итоговой суммы используем формулу:
Результат расчетов:
Примечание: формула выглядит громоздкой несмотря на то, что для столбца «Месяц» было использовано всего 2 критерия отбора. Формулы с несколькими критериями для одного диапазона данных можно существенно сократить способом, который будет показан в следующем примере.
СУММЕСЛИМН для суммирования значений по нескольким условиям в Excel
Пример 3. Используя таблицу из второго примера определить суммарной прибыли от поставок товара №1 в Китай и Грузию на протяжении всего периода (трех месяцев).
Для нахождения искомого значения используем формулу массива (для ввода жмем CTRL+SHIFT+Enter):
Функция СУММЕСЛИМН возвращает массив значений для критериев «Китай» и «Грузия» соответственно, которые суммируются функцией СУММ.
Примечание: если критерии переданы в качестве константы массива для двух и более пар диапазон_условияN; условиеN, результат выполнения формулы будет некорректным.
Полученное значение:
Особенности использования функции СУММЕСЛИМН в Excel
Функция имеет следующую синтаксическую запись:
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
Описание аргументов:
- диапазон_суммирования – обязательный аргумент, принимающий ссылку на ячейки с числами, для которых будет вычислено суммарное значение с учетом установленных критериев;
- диапазон_условия1 – обязательный аргумент, принимающий ссылку на ячейки, в которых содержатся данные для проверки на соответствие заданному критерию;
- условие1 – обязательный аргумент, принимающий ссылку на ячейку с условием поиска, текстовую запись логического выражения (например, «>=100» или «<>0»), проверяемый текст (например, «Продавец_1», «Товар_1») или числовое значение (Например, 20), определяющие ячейки в диапазоне условия1, для которых будут отобраны и просуммированы данные из диапазона суммирования.
Последующие аргументы являются необязательными для заполнения, а их смысл соответствует аргументам диапазон_условия1; условие1. Всего может быть задано до 127 условий отбора данных для суммирования.
Примечания:
- Если в качестве аргумента диапазон_суммирования была передана ссылка на диапазон ячеек, содержащие текстовые строки или пустые значения, функция СУММЕСЛИМН будет их игнорировать.
- В качестве условий суммирования данных могут выступать числовые значения, логические выражения, текстовые строки, формулы, ссылки на ячейки, содержащие условия.
- Условия проверки в виде текста или выражений с логическими и математическими символами должны быть указаны в кавычках.
- Максимальная длина условия, переданного в качестве аргумента условие1 (условие2 и т. д.) не должна превышать 255 символов.
- Для создания неточных текстовых критериев поиска требуемых значений можно использовать подстановочные знаки «?» и «*», замещающие один или несколько символов соответственно.
- Если в диапазоне ячеек, переданных в качестве аргумента диапазон_суммирования, есть ячейки, содержащие логические ИСТИНА или ЛОЖЬ, они будут автоматически преобразованы к числовым значениям 1 и 0 соответственно и будут учтены в итоговой сумме.
- Число ячеек в диапазонах суммирования и условиях должно быть одинаковым, иначе функция СУММЕСЛИМН вернет код ошибки #ЗНАЧ!.
На чтение 8 мин. Просмотров 36.3k.
Содержание
- Сумма, если ячейки содержат звездочку
- Сумма, если ячейки содержат X и Y
- Сумма, если ячейки содержат либо x, либо Y
- Сумма, если ячейки содержат определенный текст
- Сумма, если ячейка заканчивается определенным текстом
- Сумма, если равен х или у
- Сумма, если ячейки не пустые
Сумма, если ячейки содержат звездочку
=СУММЕСЛИ(диапазон;»*~**»;ранг_суммы)
Чтобы посчитать сумму, если ячейки содержат звездочку, можно использовать функцию СУММЕСЛИ со специальным символом «тильда» — «~».
В примере показано, ячейка G6 содержит эту формулу:
=СУММЕСЛИ(С5:С11;»*~**»;D5:D11)
Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «*».
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символов» и так далее.
Потому как звездочки и вопросительные знаки, сами по себе являются подстановочными знаками, если вы хотите искать эти символы в частности, вы должны защитить их с тильдой (~). Тильда причины Excel следует рассматривать следующий символ буквально.
В этом случае мы используем «~*», чтобы соответствовать символу звездочки, но он окружен звездочками по бокам, для того чтобы соответствовать звездочкам в любом месте ячейки. Если вы просто хотите, чтобы соответствовало звездочкам на конце ячейки, используйте: «*~**» по критериям.
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. С СУММЕСЛИМН, диапазон сумма всегда стоит на первом месте в списке аргументов, затем пара/диапазон критериев:
=СУММЕСЛИМН(D5:D11;С5:С11;»*~**»)
Сумма, если ячейки содержат X и Y
=СУММЕСЛИМН(диап1;диап2;»*кошка*»;диап2;»*крыса*»)
Чтобы посчитать сумму, если ячейки содержат X и Y (т. е. содержат «кошка» и «крыса», в одной ячейке) можно использовать функцию СУММЕСЛИМН.
В показанном примере, формула в F5:
=СУММЕСЛИМН(С5:С9;В5:В9;»*кошка*»;В5:В9;»*крыса*»)
Функция СУММЕСЛИМН основана на логике и такое поведение является автоматическим. Нам просто нужно поставить два диапазона/критерии пары, работающие на одном диапазоне (В5:В9).
Для обоих критериев (содержит «крыса», содержится «кошка») мы используем звездочку, которая является подстановочным, что означает «один или несколько символов». Мы ставим звездочку в начале и в конце, чтобы позволить формуле проверить, чтобы соответствовало «кошка» и «крыса» везде, где они появляются в ячейке.
Когда оба критерия рассчитывает Истина в том же ряду, СУММЕСЛИМН суммирует значения в столбец C.
Обратите внимание, что СУММЕСЛИМН не чувствителен к регистру.
Сумма, если ячейки содержат либо x, либо Y
=СУММПРОИЗВ(—((ЕЧИСЛО(ПОИСК(«кошка»;диап1)) + ЕЧИСЛО(ПОИСК(«крыса»;диап1)))>0);диап2)
Чтобы посчитать сумму, если ячейки содержат либо одну текстовую строку или другую (т. е. содержащие «кошка» или «крыса») вы можете использовать функция СУММПРОИЗВ.
Когда вы суммируете ячейки с критерием «или», вы должны быть осторожны, чтобы не произошел двойной счет, когда существует вероятность того, что оба критерия будет рассчитывать Истина. В показанном примере, мы хотим просуммировать значения в столбце C, когда ячейки в столбце B содержат слово «кошка» или «крыса». Мы не можем использовать СУММЕСЛИМН по двум критериям, т. к. СУММЕСЛИМН основан на логике. И если мы попытаемся использовать две функции СУММЕСЛИМН (т.е. СУММЕСЛИМН + СУММЕСЛИМН) у нас будет двойной счет, потому что есть клетки, которые содержат как «кошка», так и «крыса»
Одно из решений-использовать функцию СУММПРОИЗВ с ЕЧИСЛО + ПОИСК или НАЙТИ.
=СУММПРОИЗВ(—((ЕЧИСЛО(ПОИСК(«кошка»;В5:В9)) + ЕЧИСЛО(ПОИСК(«крыса»;В5:В9)))>0);С5:С9)
Эта формула основана на формуле, что находит текст внутри ячейки:
ЕЧИСЛО(ПОИСК(«Азбука»;В4:В10)
При заданном диапазоне ячеек, этот фрагмент будет рассчитывать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы делаем это дважды (один раз для «кошки» и один раз для «крысы»), мы получим два массива.
Далее, мы добавляем эти массивы вместе (с +), которая создает новый единый массив чисел. Каждое число в этом массиве является результатом добавления истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит так:
{2;0;2;1;0}
Нам нужно добавить эти цифры, но мы не хотим, чтобы удвоился счет. Поэтому мы должны убедиться, что любое значение больше нуля, всего лишь раз пересчитать. Чтобы сделать это, мы превращаем все значения Истина или Ложь, проверяя массив с «>0». Это рассчитывает Истина или Ложь:
{ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}
Которые мы затем преобразовываем в 1/0 с помощью двойного отрицания (—):
{1;0;1;1;0}
Параметр чувствителен к регистру
Функция поиска не учитывает регистр. Если вам нужен чувствительный вариант, замените поиск на найти.
Сумма, если ячейки содержат определенный текст
=СУММЕСЛИ(диапазон;»*текст*»;ранг_суммы)
Чтобы посчитать сумму, если ячейки содержат определенный текст, вы можете использовать функцию СУММЕСЛИ с подстановкой.
В примере показано, ячейка G4 содержит эту формулу:
=СУММЕСЛИ(С5:С11;»*футболка*»; D5:D11)
Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «футболка». Обратите внимание, что СУММЕСЛИ-это не регистр.
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.
Нужно, чтобы соответствовали все элементы, которые содержат слово «футболка», критериям»*футболка*». Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
=СУММЕСЛИМН(D5:D11;С5:С11;»*футболка*»)
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.
Сумма, если ячейка заканчивается определенным текстом
=СУММЕСЛИ(диапазон;»текст»;ранг_суммы)
В сумме, если ячейки заканчиваются определенным текстом, можно использовать функцию СУММЕСЛИ.
В примере показано, ячейка G5 содержит эту формулу:
=СУММЕСЛИ(C5:C11; «*шапка»; D5:D11)
Эта формула суммирует ячейки именованного диапазона сумма (D5:D11), только если ячейки именованного диапазона (С5:С11) оканчиваются на «шапка».
Обратите внимание, что СУММЕСЛИ не поддерживает регистр. Критерию «*шапка» соответствует любой текст, который заканчивается «Шапка» или «шапка».
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.
Чтобы соответствовали все элементы, которые оканчиваются на «шапка» перед текстом нужно поставить звездочку (*):
пункт;»*шапка»
Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН в сумме, если клетки начинаются с. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
=СУММЕСЛИМН(объем; номенклатура;»*шапка»)
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.
Сумма, если равен х или у
Если вам необходимо суммировать числа, основанные на других ячейках равных либо одному значению или другому (х или у), вы можете использовать функцию СУММЕСЛИ.
В примере, мы подсчитываем все продажи либо от Западного или Северного региона. Формула в ячейке G6 является:
=СУММЕСЛИ(C5:C14; «Западный»; E5:E14)+СУММЕСЛИ(C5:C14; «Северный»; E5:E14)
Каждый экземпляр СУММЕСЛИ обеспечивает промежуточный итог, один для продаж на Западе, один для продаж на Севере. Формула просто добавляет эти два результата вместе.
СУММЕСЛИ с аргументом массива
Более элегантное решение, чтобы дать функции СУММЕСЛИ более одного значения для критериев, использовать константу массива. Чтобы сделать это, постройте нормальный СУММЕСЛИ, но пакет критериев в синтаксисе массива — фигурные скобки, с отдельными элементами, разделенные запятыми. И, наконец, обернуть всю функцию СУММЕСЛИ в функцию СУММ. Это необходимо, потому что СУММЕСЛИ будет рассчитывать один результат для каждого элемента массива критериев. Они должны быть добавлены вместе, чтобы получить один результат.
Формула:
= СУММ(СУММЕСЛИ( область ; { «Западный» ; «Северный» } ; сумма ))
СУММПРОИЗВ альтернатива
Вы можете также использовать СУММПРОИЗВ для подсчета ячеек с логикой ИЛИ. Формула:
=СУММПРОИЗВ( количество * (( регион = «Западный» ) + ( регион = «Северный» )))
Это также может быть записано в виде:
=СУММПРОИЗВ( количество * ( регион = { «Западный» ; «Северный» } ))
СУММПРОИЗВ не так быстра, как СУММЕСЛИ, но разница в скорости не заметна с меньшими наборами данных.
Сумма, если ячейки не пустые
=СУММЕСЛИ( диапазон ; «<>» ; суммарный_диапазон )
Для подсчита ячеек, когда определенные значения не являются пустыми, вы можете использовать функцию СУММЕСЛИ.
В показанном примере, ячейка G4 содержит следующую формулу:
=СУММЕСЛИ( C5: C11 ; «<>» ; D5: D11 )
Эта формула суммирует суммы в колонке D только тогда, когда значение в столбце С не пусто
Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, в том числе не равно к, который вводится в <>.
При использовании оператора в критериях функции как СУММЕСЛИ, необходимо заключить его в двойные кавычки ( «»). При использовании только «<>» в качестве критерия, вы можете думать о значении как «не равно пустой», или «не пусто».
Альтернатива с СУММЕСЛИМН
Вы можете также использовать функцию суммы СУММЕСЛИМН, если ячейки не являются пустыми. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эта формула СУММЕСЛИМН эквивалентна формуле СУММЕСЛИ выше:
=СУММЕСЛИМН( D5: D11 ; C5: C11 ; «<>» )
С СУММЕСЛИМН диапазон сумма всегда стоит первым.
Функцию СУММЕСЛИ или СУММЕСЛИМН легко использовать для суммирования значений на основе заданных критериев в Excel. Помимо этих двух функций, это руководство также предоставляет удивительный инструмент, который поможет вам суммировать, если ячейки содержат определенный текст в другой ячейке, всего за несколько щелчков мышью.
Сумма значений, если ячейка содержит текст в другой ячейке с формулой
Легко суммируйте значения, если ячейка содержит текст в другой ячейке, с помощью удивительного инструмента
Больше руководств по Sum…
Сумма значений, если ячейка содержит текст в другой ячейке с формулой
Используйте следующие формулы для суммирования значений, если ячейка содержит текст в другой ячейке Excel.
1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу СУММЕСЛИ и нажмите Enter .
=SUMIF(A2:B10,»*»&D2&»*»,B2:B10)
Наконечник: Эта формула СУММЕСЛИМН также может помочь в решении проблемы.
=SUMIFS(B2:B10,A2:A10,»*»&D2&»*»)
Примечание: Вы можете видеть, что в формулах выше есть подстановочные знаки. Звездочка * обозначает любое количество символов. В этом случае * KTE * будет соответствовать тексту «KTE» в любом месте ячейки, например, KTE A (ячейка начинается с KTE), A KTE (ячейка заканчивается на KTE), а также A KTE A (KTE находится в середина текстов).
Легко суммируйте значения, если ячейка содержит текст в другой ячейке, с помощью удивительного инструмента
Здесь настоятельно рекомендуется Поиск и сумма Функция Kutools for Excel для вас. Вы можете суммировать значения, если ячейка содержит текст в другой ячейке, всего в несколько кликов. Пожалуйста, сделайте следующее.
1. Включите эту функцию, нажав Кутулс > Супер ПОСМОТРЕТЬ > LOOKUP и Sum. Смотрите скриншот:
2. в ПРОСМОТР и сумма диалоговом окне, пожалуйста, настройте следующие параметры.
- 2.1) Выберите Найти и суммировать совпавшие значения в строке (ах) вариант в Поиск и тип суммы раздел;
- 2.2). Значения поиска выберите ячейки, содержащие критерии, по которым вы будете суммировать ячейки (в данном случае я выбираю ячейку D2);
- 2.3). Выходной диапазон поле выберите пустую ячейку для вывода результата (здесь я выбираю ячейку E2);
- 2.4). Диапазон таблицы данных поле выберите данные всей таблицы без строки заголовка (здесь я выбираю A2: B10);
- 2.5) Выбрать Вернуть сумму всех совпавших значений в Опции раздел;
- 2.6) Нажмите OK кнопка. Смотрите скриншот:
Затем формула создается автоматически, и результат помещается в выбранную ячейку.
Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Статьи по теме:
Объедините повторяющиеся строки и просуммируйте значения в Excel
В Excel вы всегда можете столкнуться с этой проблемой, когда у вас есть диапазон данных, содержащий несколько повторяющихся записей, и теперь вы хотите объединить повторяющиеся данные и суммировать соответствующие значения в другом столбце. Как бы вы могли решить эту проблему? Методы, описанные в этом руководстве, помогут вам решить эту проблему.
Суммировать значения на основе критериев в другом столбце Excel
Иногда вы хотите суммировать значения на основе критериев в другом столбце, например, здесь я хочу только суммировать столбец «Объем продаж», где соответствующий столбец «Продукт» равен «А», как вы можете это сделать? Конечно, вы можете суммировать их один за другим, но здесь я предлагаю вам несколько простых методов суммирования значений в Excel.
Подсчет и суммирование ячеек на основе цвета фона в Excel
Предположим, у вас есть диапазон ячеек с разными цветами фона, такими как красный, зеленый, синий и т. Д., Но теперь вам нужно подсчитать, сколько ячеек в этом диапазоне имеют определенный цвет фона, и суммировать цветные ячейки с тем же определенным цветом. . В Excel нет прямой формулы для вычисления суммы и количества цветных ячеек, здесь в этом руководстве представлены некоторые способы решения этой проблемы.
Sumif смежная ячейка равна, пуста или содержит текст в Excel
При использовании Microsoft Excel вам может потребоваться суммировать значения, в которых соседняя ячейка соответствует критерию в определенном диапазоне, или суммировать значения, в которых соседняя ячейка пуста или содержит текст. В этом руководстве мы предоставим вам формулы для решения этих проблем.
Больше руководств по Sum…
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для 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. Например, условия могут вводится в следующем виде: 32, «>32», B4, «яблоки» или «32». |
|
Диапазон_условия2, Условие2, … (необязательный аргумент) |
Дополнительные диапазоны и условия для них. Можно ввести до 127 пар диапазонов и условий. |
Примеры
Чтобы использовать эти примеры в Excel, выделите нужные данные в таблице, щелкните их правой кнопкой мыши и выберите команду Копировать. На новом листе щелкните правой кнопкой мыши ячейку A1 и в разделе Параметры вставки выберите команду Использовать форматы конечных ячеек.
|
Проданное количество |
Продукт |
Продавец |
|---|---|---|
|
5 |
Яблоки |
Артем |
|
4 |
Яблоки |
Ольга |
|
15 |
Артишоки |
Артем |
|
3 |
Артишоки |
Ольга |
|
22 |
Бананы |
Артем |
|
12 |
Бананы |
Ольга |
|
10 |
Морковь |
Артем |
|
33 |
Морковь |
Ольга |
|
Формула |
Описание |
|
|
=СУММЕСЛИМН(A2:A9; B2:B9; «=Я*»; C2:C9; «Артем») |
Суммирует количество продуктов, названия которых начинаются с Я и которые были проданы продавцом Артем. Подстановочный знак (*) в аргументе Условие1 («=Я*») используется для поиска соответствующих названий продуктов в диапазоне ячеек, заданных аргументом Диапазон_условия1 (B2:B9). Кроме того, функция выполняет поиск имени «Артем» в диапазоне ячеек, заданных аргументом Диапазон_условия2 (C2:C9). Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 20. |
|
|
=СУММЕСЛИМН(A2:A9; B2:B9; «<>Бананы»; C2:C9; «Артем») |
Суммирует количество продуктов, которые не являются бананами и которые были проданы продавцом по имени Артем. С помощью оператора <> в аргументе Условие1 из поиска исключаются бананы («<>Бананы»). Кроме того, функция выполняет поиск имени «Артем» в диапазоне ячеек, заданных аргументом Диапазон_условия2 (C2:C9). Затем функция суммирует соответствующие обоим условиям значения в диапазоне ячеек, заданном аргументом Диапазон_суммирования (A2:A9). Результат — 30. |
Распространенные неполадки
|
Проблема |
Описание |
|---|---|
|
Вместо ожидаемого результата отображается 0 (нуль). |
Если выполняется поиск текстовых значений, например имени человека, убедитесь в том, что значения аргументов Условие1, 2 заключены в кавычки. |
|
Неверный результат возвращается в том случае, если диапазон ячеек, заданный аргументом Диапазон_суммирования, содержит значение ИСТИНА или ЛОЖЬ. |
Значения ИСТИНА и ЛОЖЬ в диапазоне ячеек, заданных аргументом Диапазон_суммирования, оцениваются по-разному, что может приводить к непредвиденным результатам при их суммировании. Ячейки в аргументе Диапазон_суммирования, которым присвоено значение ИСТИНА, оцениваются как 1. Ячейки, которым присвоено значение ЛОЖЬ, оцениваются как 0 (ноль). |
Рекомендации
|
Необходимые действия |
Описание |
|---|---|
|
Использование подстановочных знаков |
Подстановочные знаки, такие как вопросительный знак (?) или звездочка (*), в аргументах Условие1, 2 можно использовать для поиска сходных, но не совпадающих значений. Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед вопросительным знаком. Например, формула =СУММЕСЛИМН(A2:A9; B2:B9; «=Я*»; C2:C9; «Арте?») будет суммировать все значения с именем, начинающимся на «Арте» и оканчивающимся любой буквой. |
|
Различия между функциями СУММЕСЛИ и СУММЕСЛИМН |
Порядок аргументов в функциях СУММЕСЛИ и СУММЕСЛИМН различается. Например, в функции СУММЕСЛИМН аргумент Диапазон_суммирования является первым, а в функции СУММЕСЛИ — третьим. Этот момент часто является источником проблем при использовании данных функций. При копировании и изменении этих похожих формул нужно следить за правильным порядком аргументов. |
|
Одинаковое количество строк и столбцов для аргументов, задающих диапазоны ячеек |
Аргумент Диапазон_условия должен иметь то же количество строк и столбцов, что и аргумент Диапазон_суммирования. |
К началу страницы
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Просмотрите видео об использовании усложненных функций ЕСЛИ, таких как функция СУММЕСЛИМН.
Функция СУММЕСЛИ суммирует только те значения, которые соответствуют одному условию
Функция СУММКВ суммирует несколько значений, предварительно возведя каждое из них в квадрат
Функция СЧЁТЕСЛИ подсчитывает только те значения, которые соответствуют одному условию
Функция СЧЁТЕСЛИМН подсчитывает только те значения, которые соответствуют нескольким условиям
Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздние версии)
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Математические и тригонометрические функции
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
Содержание
- Сумма, если текст содержит
- С помощью ? Подстановочный знак
- Использование символа ~ (тильда)
- Объединение подстановочных знаков СУММЕСЛИМН со ссылками на ячейки
- Блокировка ссылок на ячейки
- Сумма, если ячейка содержит определенный текст с использованием подстановочных знаков в Google Таблицах
Скачать пример рабочей книги
Загрузите образец книги
В этом руководстве будет показано, как использовать функцию СУММЕСЛИМН и подстановочные знаки для суммирования данных, соответствующих ячейкам, содержащим определенный текст в Excel и Google Таблицах.
Сумма, если текст содержит
Сначала мы продемонстрируем, как суммировать данные, относящиеся к ячейкам, содержащим определенный текст, с помощью функции СУММЕСЛИМН.
Функция СУММЕСЛИМН суммирует строки данных, которые соответствуют определенным критериям. Его синтаксис:
Этот пример суммирует все Очки с Название штата который содержит «Дакота» с использованием функции СУММЕСЛИМН и подстановочного знака *.
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «* Дакота *») |
Символ * позволяет использовать любое количество (включая ноль) других символов.
В этом примере он используется для поиска всех ячеек, содержащих текст «Дакота». Этот поиск не чувствителен к регистру, поэтому «дакота» считается тем же, что и «Дакота» или «ДАКОТА». Северная Дакота а также Северная Дакота содержат «Дакота» и поэтому включены в сумму.
Сумма, если текст начинается с
Символ * также можно использовать для поиска ячеек, которые начинаются с указанного текста:
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «Новый *») |
Нью-Йорк, Нью-Джерси, а также Нью-Мексико начинаются с «Новый» и поэтому включаются в сумму. Обратите внимание, что при поисковом запросе «Новый *» текстовая ячейка должна начинаться с «Новый»; просто содержать эти символы недостаточно.
Сумма, если текст заканчивается
Точно так же мы можем просуммировать все Очки для состояния оканчивается на «o», используя:
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «* o») |
Нью-Мексико а также Огайо оканчиваются на «о» и поэтому включаются в сумму.
С помощью ? Подстановочный знак
? Символ может использоваться для представления любого отдельного символа в текстовой строке.
Этот пример находит все Имена штатов начиная с «New», за которым следуют ровно 7 символов (включая пробелы).
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «Новый ???????») |
Нью-Джерси а также Нью-Мексико соответствуют этим критериям, но Нью-Йорк нет, так как после слова «Новый» в Нью-Йорк.
Обратите внимание, что * и? Подстановочные знаки можно комбинировать, если требуется для создания очень конкретных команд поиска. Следующий пример находит Имена штатов которые начинаются с «N» и содержат «о» перед последним символом строки. Это исключает Нью-Мексико; он начинается с «N», но не имеет «о» перед последним символом.
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «N * o? *») |
Эта функция особенно полезна при поиске по кодам продуктов, почтовым индексам или серийным номерам, где положение каждого символа имеет определенное значение.
Использование символа ~ (тильда)
Специальный символ ~ (известный как тильда) позволяет нам лечить * или? символы, как если бы они были простыми текстовыми значениями и не ведут себя как подстановочные знаки.
В приведенном ниже примере нам нужно просуммировать Уровень запасов когда наименование товара конкретно соответствует тексту «Продукт?»:
| 1 | = СУММЕСЛИМН (C3: C8; B3: B8; «Продукт ~?») |
Знак ~ непосредственно перед * или? символ превращает его обратно в текстовое значение, поэтому поисковый запрос «Продукт ~?» используется для поиска точного соответствия текста запросу «Продукт?».
Объединение подстановочных знаков СУММЕСЛИМН со ссылками на ячейки
Обычно жестко записывать значения в формулы не рекомендуется. Вместо этого более гибко использовать отдельные ячейки для указания значений для наших условий поиска.
Чтобы определить, содержат ли ячейки текст, содержащийся в ячейке E3, мы можем использовать функцию СУММЕСЛИМН со ссылкой на ячейку и подстановочными знаками *:
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «*» & E3 & «*») |
Обратите внимание, что текст «Дакота» был заменен ссылкой на ячейку & E3 &, а символы * были заключены в кавычки (»«).
Множественные ссылки на ячейки и подстановочные знаки также можно комбинировать. Найти Имена штатов которые начинаются с текста в ячейке E3 и содержат текст в ячейке F3, за которым следует как минимум еще 1 символ, можно использовать следующую формулу:
| 1 | = СУММЕСЛИМН (C3: C9, B3: B9, E3 & «*» & F3 & «? *») |
Блокировка ссылок на ячейки
Чтобы наши формулы было легче читать, мы показали формулы без заблокированных ссылок на ячейки:
| 1 | = СУММЕСЛИМН (C3: C9; B3: B9; «*» & E3 & «*») |
Но эти формулы не будут работать должным образом при копировании и вставке в другое место в файле. Вместо этого вы должны использовать ссылки на заблокированные ячейки следующим образом:
| 1 | = СУММЕСЛИМН ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, «*» & E3 & «*») |
Прочтите нашу статью о блокировке ссылок на ячейки, чтобы узнать больше.
Эти формулы работают в Google Таблицах точно так же, как и в Excel.
