Excel SUMIFS: как суммировать только значения, отвечающие нескольким критериям

Функция SUMIFS расширяет полезность функции SUMIF, позволяя вам указать от 2 до 127 критериев, а не только один. Например, функция SUMIF может устанавливать критерии для продавцов, которые продали более 250 единиц. Функция SUMIFS могла бы соответствовать критериям продавцов из определенного региона, которые продали более 250 единиц.

Примечание . Эти инструкции относятся к Excel 2019, 2016, 2013, 2010, Excel 2019 для Mac, Excel 2016 для Mac, Excel для Mac 2011, Excel для Office 365 и Excel Online.

Как работает функция SUMIFS

Обычно SUMIFS работает со строками данных, называемыми записями. В записи все данные в каждой ячейке или поле в строке связаны, например, название компании, адрес и номер телефона.

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

В пошаговом руководстве SUMIF мы соответствовали единственному критерию торговых агентов, которые продали более 250 заказов в год.

В этом уроке мы установим два условия с использованием SUMIFS: для торговых агентов в восточном регионе продаж, у которых было меньше 275 продаж в прошлом году.

Установка более двух условий может быть выполнена путем указания дополнительных аргументов Criteria_range и Criteria для SUMIFS.

Ввод данных учебника

Первый шаг к использованию функции SUMIFS в Excel – это ввод данных.

Введите данные в ячейки от D1 до F11 рабочего листа Excel, как показано на рисунке выше.

Функция SUMIFS и критерии поиска (менее 275 заказов и торговых агентов из восточного региона продаж) располагаются в строке 12 под данными.

Примечание . Инструкции по обучению не содержат этапов форматирования рабочего листа. Хотя форматирование не повлияет на завершение учебника, ваш рабочий лист будет отличаться от показанного в примере. Функция SUMIFS даст вам те же результаты.

Синтаксис функции SUMIFS

В Excel синтаксис функции относится к макету функции и включает в себя имя функции, скобки и аргументы.

Синтаксис для функции SUMIFS:

 = SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...) 
 

В функции можно указать до 127 пар Criteria_range / Criteria .

Аргументы функции SUMIFS

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

Все аргументы в этой функции обязательны.

Sum_range: данные в этом диапазоне ячеек суммируются, когда найдено совпадение между всеми указанными критериями и их соответствующими аргументами Criteria_range.

Criteria_range: группа ячеек, в которой функция должна искать соответствие соответствующему аргументу Criteria.

Критерии: это значение сравнивается с данными в соответствующем.

Criteria_range: фактические данные или ссылка на ячейку для данных аргумента.

Запуск функции SUMIFS

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

1. Щелкните ячейку F12, чтобы сделать ее активной ячейкой; F12 – это место, где вы войдете в функцию SUMIFS.
2. Откройте вкладку Формулы .
3. Нажмите Математика и триггеры в группе «Библиотека функций».
4. Нажмите SUMIFS в списке, чтобы открыть диалоговое окно функции SUMIFS.

В Excel Online нет вкладки “Формулы”. Чтобы использовать SUMIFS в Excel Online, перейдите в раздел Вставить > Функция .

1. Нажмите на ячейку F12 , чтобы сделать ее активной, чтобы вы могли войти в функцию SUMIFS.
2. Нажмите кнопку Вставить функцию . Откроется диалоговое окно «Вставить функцию».
3. Нажмите Математика и триггеры в списке категорий.
4. Нажмите SUMIFS в списке, чтобы запустить функцию.

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

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

Ввод аргумента Sum_range

Аргумент Sum_range содержит ссылки на ячейки данных, которые мы хотим добавить.

В этом руководстве данные для аргумента Sum_range помещаются в столбец Total Sales.

Учебное пособие

1. Нажмите на строку Sum_range в диалоговом окне.
2. Выделите ячейки от F3 до F9 на листе, чтобы добавить эти ссылки на ячейки в строку Sum_range.

Ввод аргумента Criteria_range1

В этом уроке мы пытаемся соответствовать двум критериям в каждой записи данных:

1. Торговые агенты из Восточного региона продаж
2. Торговые агенты, которые сделали менее 275 продаж в этом году

Аргумент Criteria_range1 указывает диапазон ячеек, которые SUMIFS должен искать при попытке соответствовать первому критерию: восточный регион продаж.

Учебное пособие

1. Нажмите на строку Criteria_range1 в диалоговом окне.
2. Выделите ячейки от D3 до D9 на листе, чтобы ввести эти ссылки на ячейки в качестве диапазона, который будет искать функция.

Ввод аргумента Критерий1

Первый критерий, которому мы хотим соответствовать, – это если данные в диапазоне D3: D9 равны Восток .

Хотя фактические данные, такие как слово Восток , могут быть введены в диалоговое окно для этого аргумента, обычно лучше добавить данные в ячейку на рабочем листе, а затем ввести эту ссылку на ячейку в диалоговое окно. коробка.

Учебное пособие

1. Нажмите на строку Критерии1 в диалоговом окне.
2. Нажмите на ячейку D12 , чтобы ввести ссылку на эту ячейку. Функция будет искать диапазон, выбранный на предыдущем шаге, для данных, соответствующих этим критериям.

Как ссылки на ячейки увеличивают универсальность SUMIFS

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

Таким образом, после определения суммы продаж для региона Восток будет легко найти те же данные для другого региона продаж, просто изменив восток на север или запад в ячейке D12. Функция автоматически обновит и отобразит новый результат.

Ввод аргумента Criteria_range2

Аргумент Criteria_range2 указывает диапазон ячеек, которые SUMIFS должен искать при попытке соответствовать второму критерию: торговые агенты, которые продали менее 275 заказов в этом году.

1. Нажмите на строку Criteria_range2 в диалоговом окне.
2. Выделите ячейки от E3 до E9 на рабочем листе, чтобы ввести эти ссылки на ячейки в качестве второго диапазона, который будет искать функция.

Ввод критерия2 критерия

Второй стандарт, к которому мы стремимся соответствовать, – это если данные в диапазоне E3: E9 меньше 275 заказов на продажу.

Как и в случае с аргументом Criteria1, мы введем ссылку на ячейку для местоположения Criteria2 в диалоговое окно, а не на сами данные.

1. Нажмите на строку Критерии2 в диалоговом окне.
2. Нажмите на ячейку E12 , чтобы ввести ссылку на эту ячейку. Функция будет искать диапазон, выбранный на предыдущем шаге, для данных, которые соответствуют критериям.
3. Нажмите ОК , чтобы завершить функцию SUMIFS и закрыть диалоговое окно.

Ответ ноль (0) появится в ячейке F12 (ячейка, в которую мы ввели функцию), потому что мы еще не добавили данные в поля Criteria1 и Criteria2 (C12 и D12). Пока мы этого не сделаем, функция не будет суммироваться, поэтому итоговое значение остается на нуле.

Добавление критериев поиска и завершение учебника

Последний шаг в учебнике – добавить данные в ячейки на листе, который определен как содержащий аргументы Criteria .

Для справки с этим примером см. Изображение выше.

1. В ячейке D12 введите Восток и нажмите клавишу Ввод на клавиатуре.
2. В ячейке E12 введите <275 и нажмите клавишу Enter на клавиатуре («<» - это символ меньше, чем в Excel).

Ответ $ 119 719,00 должен появиться в ячейке F12.

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

Сумма в 49 017 долл. США и 70 702 долл. США составляет 119 719 долл. США.

Если щелкнуть ячейку F12, полная функция
= SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) появится на панели формул над рабочим листом.