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

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

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

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

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

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

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

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

• диапазон: Диапазон данных, который вы хотите оценить по критерию;
• "*"&ячейка&"*": Критерий, на основе которого вы хотите суммировать значения;
  • *: Подстановочный знак, который находит любое количество символов.
  • ячейка: Ячейка содержит конкретный текст для поиска.
  • &: Этот оператор конкатенации (&) используется для объединения ссылки на ячейку со звездочками.
• сум_диапазон: Диапазон ячеек с соответствующими числовыми значениями, которые вы хотите суммировать.

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

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

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

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

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

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

Объяснение этой формулы:

=СУММПРОИЗВ($B$2:$B$12*(ЕЧИСЛО(ПОИСК("Футболка",$A$2:$A$12))))

• ПОИСК("Футболка",$A$2:$A$12): Функция ПОИСК возвращает местоположение определенного текста «Футболка» из диапазона данных A2:A12, поэтому вы получите массив, подобный этому: {5;#ЗНАЧ!;#ЗНАЧ!;7;#ЗНАЧ!;7;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;7}.
• ЕЧИСЛО(ПОИСК("Футболка",$A$2:$A$12))= ЕЧИСЛО({5;#ЗНАЧ!;#ЗНАЧ!;7;#ЗНАЧ!;7;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;#ЗНАЧ!;7}): Эта функция ЕЧИСЛО используется для проверки числовых значений и возвращает новый массив: {ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА}.
• $B$2:$B$12*(ЕЧИСЛО(ПОИСК("Футболка",$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}.
• СУММПРОИЗВ($B$2:$B$12*(ЕЧИСЛО(ПОИСК("Футболка",$A$2:$A$12)))) =СУММПРОИЗВ({347;0;0;430;0;379;0;0;0;0;449}): Наконец, функция СУММПРОИЗВ суммирует все значения в массиве, чтобы получить результат: 1605.

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

Больше статей: