Функция 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.
Критерии — это значение сравнивается с данными в соответствующем.
Запуск функции SUMIFS
Хотя можно просто использовать функцию SUMIFS непосредственно в ячейке на рабочем листе, многим людям проще использовать диалоговое окно функции для входа в функцию.
- Щелкните ячейку F12, чтобы сделать ее активной ячейкой ; F12 — это место, где вы войдете в функцию SUMIFS.
- Нажмите вкладку Формулы .
- Нажмите Math & Trig в группе «Библиотека функций».
- Нажмите SUMIFS в списке, чтобы открыть диалоговое окно функции SUMIFS.
В Excel Online нет вкладки «Формулы». Чтобы использовать SUMIFS в Excel Online, выберите « Вставка» > « Функция» .
- Щелкните ячейку F12, чтобы сделать ее активной, чтобы вы могли войти в функцию SUMIFS.
- Нажмите кнопку « Вставить функцию» . Откроется диалоговое окно «Вставить функцию».
- Нажмите Math & Trig в списке категорий.
- Нажмите SUMIFS в списке, чтобы запустить функцию.
Данные, которые мы вводим в пустые строки в диалоговом окне, будут формировать аргументы функции SUMIFS.
Эти аргументы сообщают функции, для каких условий мы тестируем и какой диапазон данных суммировать, когда она удовлетворяет этим условиям.
Ввод аргумента Sum_range
Диапазон_суммирования аргумент содержит ссылки на ячейки с данными , которые мы хотим добавить вверх.
В этом руководстве данные для аргумента Sum_range помещаются в столбец Total Sales.
Шаги учебника
- Нажмите на строку Sum_range в диалоговом окне.
- Выделите ячейки от F3 до F9 на листе, чтобы добавить эти ссылки на ячейки в строку Sum_range.
Ввод аргумента Criteria_range1
В этом уроке мы пытаемся соответствовать двум критериям в каждой записи данных:
- Торговые агенты из Восточного региона продаж
- Торговые агенты, которые сделали менее 275 продаж в этом году
Аргумент Criteria_range1 указывает диапазон ячеек, которые SUMIFS должен искать при попытке соответствовать первому критерию: восточный регион продаж.
Шаги учебника
- Нажмите на строку Criteria_range1 в диалоговом окне.
- Выделите ячейки от D3 до D9 на листе, чтобы ввести эти ссылки на ячейки в качестве диапазона, который будет искать функция.
Ввод аргумента Критерий1
Первый критерий, который мы ищем, это соответствие данных в диапазоне D3: D9 востоку .
Хотя фактические данные, такие как слово « восток» , могут быть введены в диалоговое окно для этого аргумента, обычно лучше добавить данные в ячейку на рабочем листе, а затем ввести эту ссылку на ячейку в диалоговом окне.
Шаги учебника
- Нажмите строку Criteria1 в диалоговом окне.
- Нажмите на ячейку D12, чтобы ввести ссылку на эту ячейку. Функция будет искать диапазон, выбранный на предыдущем шаге, для данных, соответствующих этим критериям.
Как ссылки на ячейки увеличивают универсальность SUMIFS
Если ссылка на ячейку, такая как D12, введена в качестве аргумента критерия , функция SUMIFS будет искать совпадения с любыми данными в этой ячейке на листе.
Таким образом, после определения суммы продаж для восточного региона, будет легко найти те же данные для другого региона продаж, просто изменив восток на север или запад в ячейке D12. Функция автоматически обновит и отобразит новый результат.
Ввод аргумента Criteria_range2
Аргумент Criteria_range2 указывает диапазон ячеек, которые SUMIFS должен искать, пытаясь соответствовать второму критерию: торговые агенты, которые продали менее 275 заказов в этом году.
- Нажмите на строку Criteria_range2 в диалоговом окне.
- Выделите ячейки E3-E9 на листе, чтобы ввести эти ссылки на ячейки в качестве второго диапазона, который будет искать функция.
Ввод аргумента Критерий2
Второй стандарт, к которому мы стремимся соответствовать, — это если данные в диапазоне E3: E9 меньше 275 заказов на продажу.
Как и в случае с аргументом Criteria1, мы введем ссылку на ячейку для местоположения Criteria2 в диалоговое окно, а не на сами данные.
- Нажмите строку Criteria2 в диалоговом окне.
- Нажмите на ячейку E12, чтобы ввести ссылку на эту ячейку. Функция будет искать диапазон, выбранный на предыдущем шаге, для данных, которые соответствуют критериям.
- Нажмите OK, чтобы завершить функцию SUMIFS и закрыть диалоговое окно.
Ответ ноль (0) появится в ячейке F12 (ячейка, в которую мы ввели функцию), потому что мы еще не добавили данные в поля Criteria1 и Criteria2 (C12 и D12). Пока мы этого не сделаем, функция не будет суммироваться, поэтому итоговое значение остается на нуле.
Добавление критериев поиска и завершение учебника
Последний шаг в учебнике — добавить данные в ячейки на рабочем листе, который определен как содержащий аргументы критерия .
Для справки с этим примером см. Изображение выше.
- В ячейке D12 введите Восток и нажмите клавишу Enter на клавиатуре.
- В ячейке 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) появится на панели формул над рабочим листом.