KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Суммирование, если ячейки содержат или равны либо x, либо y в Excel

АвторSiluviaДата изменения

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

Как просуммировать ячейки, содержащие либо x, либо y в Excel?
Как просуммировать ячейки, равные либо x, либо y в Excel?

Как просуммировать ячейки, содержащие либо «x», либо «y», в Excel?

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

doc-sum-if-cells-contain-either-x-or-y-1

Универсальные формулы

=SUMPRODUCT(--((ISNUMBER(SEARCH("criteria1",rng1)) + ISNUMBER(SEARCH("criteria2",rng2)))>,0),sum_rng)

Аргументы

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

Как использовать эту формулу?

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

=SUMPRODUCT(--((ISNUMBER(SEARCH(G4,C5:C12))+ISNUMBER(SEARCH(G5,C5:C12)))>,0),D5:D12)

doc-sum-if-cells-contain-either-x-or-y-2

Примечания: В этой формуле G4 и G5 — ячейки, содержащие два условия. Кроме того, вы можете напрямую ввести нужные текстовые значения, заключив их в двойные кавычки.

=SUMPRODUCT(--((ISNUMBER(SEARCH("apple",C5:C12))+ISNUMBER(SEARCH("lychee",C5:C12)))>,0),D5:D12)

Объяснение формулы

=SUMPRODUCT(--((ISNUMBER(SEARCH("apple",C5:C12))+ISNUMBER(SEARCH("lychee",C5:C12)))>,0),D5:D12)

ISNUMBER(SEARCH(«apple»,C5:C12))
>SEARCH(«apple»,C5:C12): Функция ПОИСК определяет позицию первого символа «яблоко» в каждой ячейке диапазона C5:C12 и возвращает массив следующего вида: {#ЗНАЧ!;#ЗНАЧ!;1;9;1;#ЗНАЧ!;#ЗНАЧ!;18}. Значения #ЗНАЧ! означают, что «яблоко» не найдено в соответствующей ячейке;
>ISNUMBER({#VALUE!;#VALUE!;1;9;1;#VALUE!;#VALUE!;18}): Здесь функция ЕЧИСЛО проверяет, является ли указанное значение в массиве числовым или нечисловым. Если аргумент является числом, функция возвращает «ИСТИНА», в противном случае — «ЛОЖЬ». В результате вы получите новый массив: {ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА}.
ISNUMBER(SEARCH(«lychee»,C5:C12)): Объяснение этой части аналогично приведённому выше. Здесь функция ЕЧИСЛО в итоге возвращает массив следующего вида: {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА};
{ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА} + {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА}: Затем эти массивы складываются, и математическая операция преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 соответственно. В итоге получаем числовой массив: {0;0;1;2;1;0;1;2};
({0;0;1;2;1;0;1;2}) > 0: Каждое число в массиве сравнивается с числом 0 и возвращает массив ИСТИНА/ЛОЖЬ следующего вида: {ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА};
--({ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА}): Эти два знака минуса преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. В результате вы получите новый массив: {0;0;1;1;1;0;1;1};
SUMPRODUCT({0;0;1;1;1;0;1;1},D5:D12)=SUMPRODUCT({0;0;1;1;1;0;1;1},{2000;1600;980;1200;600;900;150;300}): Функция СУММПРОИЗВ перемножает соответствующие компоненты двух массивов и возвращает массив следующего вида: {0;0;980;1200;600;0;150;300}, после чего суммирует все результаты в массиве и в итоге возвращает 3230.

Как просуммировать ячейки, равные либо x, либо y, в Excel?

В предыдущем разделе мы представили формулу для суммирования ячеек, содержащих одно из двух значений. В этом разделе показано, как объединить две функции СУММЕСЛИ, чтобы просуммировать ячейки, равные либо x, либо y.

В приведённом ниже примере суммируются продажи товаров, которые являются либо «яблоками», либо «личи».

doc-sum-if-cells-contain-either-x-or-y-3

Универсальная формула

=SUMIF(criteria_range1,"criteria1",sum_range)+SUMIF(criteria_range2,"criteria2",sum_range)

Аргументы

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

Как использовать эту формулу?

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

=SUMIF(C5:C12,G4,D5:D12)+SUMIF(C5:C12,G5,D5:D12)

doc-sum-if-cells-contain-either-x-or-y-4

Примечание: Эта формула ссылается на две ячейки — G4 и G5, в которых заданы два условия. Вы также можете напрямую указать текстовые значения в качестве критериев, заключив их в двойные кавычки прямо в формуле.

=SUMIF(C5:C12,"apple",D5:D12)+SUMIF(C5:C12,"lychee",D5:D12)

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

=SUMIF(C5:C12,"apple",D5:D12)+SUMIF(C5:C12,"lychee",D5:D12)

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

Связанные функции

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

Функция ЕЧИСЛО Excel
Функция ЕЧИСЛО в Excel возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ — во всех остальных случаях.

Функция ПОИСК Excel
Функция ПОИСК в Excel позволяет находить позицию заданного символа или подстроки внутри текстовой строки.

Функция СУММЕСЛИМН Excel
Функция СУММЕСЛИМН в Excel позволяет суммировать значения ячеек по нескольким критериям.

Связанные формулы

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

Суммирование, если ячейки содержат и x, и y
В этом пошаговом руководстве показано, как просуммировать диапазон ячеек, если соответствующие ячейки содержат одновременно и x, и y, с помощью функции СУММЕСЛИМН.

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

Суммирование, если ячейки равны или не равны определённому значению
В этом пошаговом руководстве разбираются две формулы для суммирования диапазона ячеек, значения которых равны или не равны заданному, с использованием функции СУММЕСЛИ.

Лучшие инструменты для повышения продуктивности в Office

Kutools для Excel — Помогает Вам выделяться из толпы

🤖KUTOOLS AI Помощник: Революционизируйте Анализ данных на основе:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и создания диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты  |  Удалить пустые строки  |  Объединить столбцы или ячеек без потери данных  |  Округление без использования формул…
Расширенный VLookup:Несколько критериев  |  Несколько значений  |  Между несколькими листами  |  Распознавание нечетких соответствий…
Расш. Раскрывающийся список:Простой выпадающий список  |  Зависимый выпадающий список  |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление определённого количества столбцов  |  Перемещение столбцов  |  Переключение видимости скрытых столбцов  |Сравнение столбцов для Выбрать одинаковые/разные ячейки…
Избранные функции:Сетка фокусировки  |  Просмотр дизайна  |  Улучшенная строка формулы  |  Управление книгами и листами|Библиотека ресурсов(автотекст)|  Выбор даты  |  Объединить листы  |  Шифрование/Расшифровать ячейки  |  Отправка писем по списку  |  Супер фильтр  |  Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы…)|  50+Типыдиаграмм(Диаграмма Ганта…)|  40+ Практические формулы(Рассчитать возраст на основе даты рождения…)|  19 Инструментывставки(Вставить QR-код,Вставка изображения по пути…)|  12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют…)|  7 Объединить и разделитьинструменты(Расширенное объединение строк,Разделение ячеек Excel…)|…и многое другое
Используйте Kutools на предпочитаемом языке — поддерживает английский, испанский, немецкий, французский, китайский и ещё 40+ языков!

Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…

Office Tab — Включает вкладочное чтение и редактирование в Microsoft Office (включая Excel)

  • Переключайтесь между десятками открытых документов всего за секунду!
  • Сокращает сотни кликов мышью каждый день — забудьте о «мышечной руке» раз и навсегда!
  • Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
  • Добавляет эффективность вкладок в Office (включая Excel) — как в Chrome, Edge и Firefox.