Функции и формулы

Как использовать функцию SUMIFS в Google Таблицах (несколько критериев)

Если вы хотите найти сумму значений на основе условия, вы, скорее всего, знаете о функциях SUMIF и SUMIFS в Google Таблицах. Проблема с функцией SUMIF в том, что она позволяет указать только одно условие.

Однако во многих случаях условия не такие уж и сложные. Нам часто нужно найти суммы, основанные более чем на одном условии. В таких случаях функция SUMIFS может оказаться весьма удобной.

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

Что делает функция SUMIFS?

Проще говоря, функция СУММЕСЛИМН находит сумму значений, удовлетворяющих более чем одному условию.

Функция просматривает диапазон ячеек и извлекает те, которые соответствуют всем указанным условиям.

После сканирования диапазона он суммирует полученные значения и отображает результат.

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

Синтаксис функции СУММЕСЛИМН

Общий синтаксис функции SUMIFS:

 =SUMIFS(sum_range, criteria_range1, criteria1,[ criteria_range2, criteria2, ... criteria_range_n, criteria_])

Здесь, 

  • sum_range — это диапазон ячеек, содержащий значения, которые вы хотите проверить.
  • criteria_range1 является диапазон для проверки факторам1.
  • criteria1 — это условие, которому должен удовлетворять диапазон_критериев1.
  • criteria_range2 , criteria2 и т.д. дополнительные диапазоны и критерии проверки.

Мы можем добавить столько критериев, сколько нам нужно.

Однако само собой разумеется, что в функции должен быть как минимум 1 диапазон критериев и параметр критериев.

Как использовать функцию SUMIFS в Google Таблицах

Синтаксис функции станет понятнее, когда мы поработаем над несколькими примерами.

В таблице ниже указаны 8 сотрудников с указанием их отделов, местоположения, даты присоединения, количества отработанных часов и денег, полученных от продаж.

Давайте рассмотрим несколько сценариев с использованием этих данных.

Использование SUMIFS с текстовыми условиями

Допустим, вы хотите узнать общее количество часов, отработанных всеми сотрудниками производственного отдела в Нью-Йорке. В этом случае у нас есть два условия:

  • Отдел= “Производство”
  • Местоположение= “Нью-Йорк”

 Таким образом, параметры функции SUMIFS будут следующими:

  • sum_range будет включать ячейки в ячейках E2: E9 — отработанные часы
  • criteria_range1 будет включать местоположения ячеек B2: B9 — Отдел (Department)
  • criteria1 —  будет «Производство», так как мы хотим выбрать ячейки, в которых отдел = «Производство».
  • criteria_range2 — будет включать местоположения ячеек C2: C9 — Местоположение
  • criteria2 будет «Нью-Йорк», поскольку мы хотим выбрать ячейки, в которых местоположение = «Нью-Йорк».

Таким образом, вы можете ввести следующую формулу в строке формул:

=SUMIFS(E2:E9,B2:B9,"Manufacturing",C2:C9,"New York")

Вот что вы получите в результате:

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

В приведенном выше случае функция SUMIFS проверила каждую ячейку от B2 до B9 и от C2 до C9, чтобы найти ячейки, которые удовлетворяют обоим условиям — «Производство» и «Нью-Йорк» соответственно.

Для каждой совпадающей строки функция выбрала соответствующее значение отработанных часов из столбца E.

Затем он сложил все выбранные значения отработанных часов и отобразил результат в ячейке C13.

Использование SUMIFS с условием даты

Добавим еще одно условие. Допустим, мы также хотим добавить критерии, согласно которым дата присоединения сотрудника должна быть до 1 января 2020 года.

Итак, теперь у нас есть три условия:

  • Отдел = «Производство»
  • Местоположение = «Нью-Йорк»
  • Дата присоединения <01.01.2020

Это означает, что нам нужно добавить еще два параметра в функцию SUMIFS:

  • диапазон_критерия3 будет включать местоположения ячеек D2: D9 — Дата присоединения
  • Критерий 3 будет «<01.01.2020», поскольку мы хотим выбрать ячейки, в которых дата присоединения <«01.01.2020».

Итак, вы можете ввести следующую формулу в строке формул (обратите внимание на последние 2 параметра, которые были добавлены):

=SUMIFS(E2:E9,B2:B9,"Manufacturing",C2:C9,"New York",D2:D9,"<01/01/2020")

Вот что вы получите в результате:

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

В приведенном выше случае функция SUMIFS проверила каждую ячейку от B2 до B9, от C2 до C9 и от D2 до D9, чтобы найти ячейки, которые удовлетворяют всем трем условиям — «Производство», «Нью-Йорк» и «<01.01.2020». соответственно.

Для каждой совпадающей строки функция выбрала соответствующее значение отработанных часов из столбца E.

Затем он сложил все выбранные значения отработанных часов и отобразил результат в ячейке C14.

Использование SUMIFS с числовым условием

Наконец, давайте рассмотрим случай, когда мы хотим узнать общий объем продаж сотрудников производственного сектора, которые проработали 10 часов или более.

Итак, теперь у нас есть два условия:

  • Отдел = «Производство»
  • Отработано часов> = 10

Параметры функции SUMIFS будут следующими:

  • Поскольку теперь нам нужен общий объем продаж, sum_range будет включать ячейки в местоположениях F2: F9.
  • диапазон_критерия1 будет включать местоположения ячеек B2: B9 — Отдел
  • Критерий 1 будет «Производство», так как мы хотим выбрать ячейки, в которых отдел = «Производство».
  • диапазон_критерия2 будет включать местоположения ячеек E2: E9 — Отработанные часы
  • критерий2 будет «> = 10», так как мы хотим выбрать ячейки, в которых отработано часов> = 10.

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

=SUMIFS(F2:F9,B2:B9,"Manufacturing",E2:E9,">=10")

Вот что вы получите в результате:

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

В приведенном выше случае функция SUMIFS проверила каждую ячейку от B2 до B9 и от E2 до E9, чтобы найти ячейки, которые удовлетворяют обоим условиям — «Производство» и «> = 10» соответственно.

Для каждой совпадающей строки функция выбирала соответствующее значение продаж из столбца F.

Затем он сложил все выбранные значения продаж и отобразил результат в ячейке C13.

Что следует помнить

При использовании функции SUMIFS следует помнить о нескольких важных моментах:

  • В функции SUMIFS регистр не учитывается.
  • Если критерий содержит текстовое значение, дату или символы подстановки, его необходимо заключить в двойные кавычки.
  • Ссылки на ячейки в критериях не заключаются в кавычки.
  • В вашей функции SUMIFS должна быть как минимум 1 пара критериев_диапазона и критериев.

Функция SUMIFS настолько универсальна и настраиваема, что вы можете включать любое количество условий, которые захотите.

Я рекомендую вам включить функцию SUMIFS в свои электронные таблицы, когда вам нужно найти сумму на основе более чем одного условия.

Надеюсь, этот урок был Вам полезен.

Вас могут заинтересовать следующие статьи: