Суммирование, если ячейки содержат определённый текст в другом столбце

В этом руководстве показано, как суммировать значения, если ячейки в другом столбце содержат определённый или частичный текст. Возьмём приведённый ниже диапазон данных в качестве примера: чтобы получить общую сумму товаров, содержащих текст «Футболка», в Excel можно использовать как функцию СУММЕСЛИ, так и функцию СУММПРОИЗВ.

• Суммирование значений, если ячейка содержит определённый или частичный текст, с помощью функции СУММЕСЛИ
• Суммирование значений, если ячейка содержит определённый или частичный текст, с помощью функции СУММПРОИЗВ

Суммирование значений, если ячейка содержит определённый или частичный текст, с помощью функции СУММЕСЛИ

Чтобы суммировать значения, когда ячейка в другом столбце содержит определённый текст, используйте функцию СУММЕСЛИ с подстановочным знаком (*). Общий синтаксис выглядит следующим образом:

Общая формула с жёстко заданным текстом:

• range: Диапазон данных, которые необходимо оценить с использованием критериев;
• *text*— критерий, по которому суммируются значения. Подстановочный знак * означает любое количество символов; чтобы найти все элементы, содержащие определённый текст, поместите его между двумя символами *. ()Обратите внимание: и текст, и подстановочный знак обязательно заключайте в двойные кавычки.)
• sum_range: диапазон ячеек с соответствующими числовыми значениями, которые нужно просуммировать.

Общая формула со ссылкой на ячейку:

• range: Диапазон данных, которые необходимо оценить с использованием критериев;
• "*"&cell&"*": критерий, на основе которого требуется суммировать значения;
  • * — подстановочный знак, который находит любое количество символов.
  • ячейка: ячейка содержит конкретный текст, который нужно найти.
  • &: этот оператор конкатенации (&) объединяет ссылку на ячейку со звёздочками.
• sum_range: диапазон ячеек с соответствующими числовыми значениями, которые необходимо просуммировать.

Ознакомившись с основными принципами работы функции, используйте любую из приведённых ниже формул и нажмите клавишу Enter, чтобы получить результат:

Примечание: функция СУММЕСЛИ не учитывает регистр символов.

Суммирование значений, если ячейка содержит определённый или частичный текст, с помощью функции СУММПРОИЗВ

Функция СУММПРОИЗВ также позволяет решить эту задачу без использования подстановочных знаков. Для этого достаточно вложить функции ЕЧИСЛО и ПОИСК внутрь СУММПРОИЗВ. Общий синтаксис выглядит следующим образом:

• sum_range: диапазон ячеек с соответствующими числовыми значениями, которые необходимо просуммировать;
• criteria: критерий, по которому необходимо суммировать значения. Это может быть ссылка на ячейку или заданный вами текст;
• range: Диапазон данных, которые необходимо оценить с использованием критериев;

Введите любую из приведённых ниже формул в пустую ячейку и нажмите клавишу Enter, чтобы получить результат:

Пояснение к этой формуле:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(«T-Shirt»,$A$2:$A$12))))

• SEARCH(«T-Shirt»,$A$2:$A$12): функция ПОИСК возвращает позицию текста «Футболка» в диапазоне A2:A12, формируя массив следующего вида: {5;#ЗНАЧ!;#ЗНАЧ!;7;#ЗНАЧ!;7;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;7}.
• ISNUMBER(SEARCH(«T-Shirt»,$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): функция ЕЧИСЛО проверяет, являются ли значения числами, и возвращает новый массив: {ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА}.
• $B$2:$B$12*(ISNUMBER(SEARCH(«T-Shirt»,$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА}: при умножении этих двух массивов логические значения ИСТИНА и ЛОЖЬ автоматически преобразуются в числа 1 и 0 соответственно. В результате произведение массивов примет следующий вид: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1;0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
• SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(«T-Shirt»,$A$2:$A$12)))) =SUMPRODUCT({347,0,0,430,0,379,0,0,0,0,449}): наконец, функция СУММПРОИЗВ суммирует все значения в массиве и возвращает результат — 1605.

Используемые связанные функции:

Дополнительные статьи: