Sumifs excel как пользоваться
Функция 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:
В функции можно указать до 127 пар Criteria_range / Criteria .
Аргументы функции SUMIFS
Аргументы функции сообщают ей, какие условия проверять и какой диапазон данных суммировать, когда она удовлетворяет этим условиям.
Все аргументы в этой функции обязательны.
Sum_range: данные в этом диапазоне ячеек суммируются, когда найдено совпадение между всеми указанными критериями и их соответствующими аргументами Criteria_range.
Criteria_range: группа ячеек, в которой функция должна искать соответствие соответствующему аргументу Criteria.
Критерии: это значение сравнивается с данными в соответствующем.
Criteria_range: фактические данные или ссылка на ячейку для данных аргумента.
Запуск функции SUMIFS
Хотя можно просто использовать функцию SUMIFS непосредственно в ячейке на рабочем листе, многим людям проще использовать диалоговое окно функции для входа в функцию.
- Щелкните ячейку F12, чтобы сделать ее активной ячейкой; F12 – это место, где вы войдете в функцию SUMIFS.
- Откройте вкладку Формулы .
- Нажмите Математика и триггеры в группе «Библиотека функций».
- Нажмите SUMIFS в списке, чтобы открыть диалоговое окно функции SUMIFS.
В Excel Online нет вкладки “Формулы”. Чтобы использовать SUMIFS в Excel Online, перейдите в раздел Вставить > Функция .
Данные, которые мы вводим в пустые строки в диалоговом окне, будут формировать аргументы функции SUMIFS.
Эти аргументы сообщают функции, для каких условий мы тестируем и какой диапазон данных суммировать, когда она соответствует этим условиям.
Ввод аргумента Sum_range
Аргумент 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 равны Восток .
Хотя фактические данные, такие как слово Восток , могут быть введены в диалоговое окно для этого аргумента, обычно лучше добавить данные в ячейку на рабочем листе, а затем ввести эту ссылку на ячейку в диалоговое окно. коробка.
Учебное пособие
- Нажмите на строку Критерии1 в диалоговом окне.
- Нажмите на ячейку D12 , чтобы ввести ссылку на эту ячейку. Функция будет искать диапазон, выбранный на предыдущем шаге, для данных, соответствующих этим критериям.
Как ссылки на ячейки увеличивают универсальность SUMIFS
Если ссылка на ячейку, такая как D12, введена как аргумент Критерии , функция SUMIFS будет искать совпадения с любыми данными в этой ячейке на листе.
Таким образом, после определения суммы продаж для региона Восток будет легко найти те же данные для другого региона продаж, просто изменив восток на север или запад в ячейке D12. Функция автоматически обновит и отобразит новый результат.
Ввод аргумента Criteria_range2
Аргумент Criteria_range2 указывает диапазон ячеек, которые SUMIFS должен искать при попытке соответствовать второму критерию: торговые агенты, которые продали менее 275 заказов в этом году.
- Нажмите на строку Criteria_range2 в диалоговом окне.
- Выделите ячейки от E3 до E9 на рабочем листе, чтобы ввести эти ссылки на ячейки в качестве второго диапазона, который будет искать функция.
Ввод критерия2 критерия
Второй стандарт, к которому мы стремимся соответствовать, – это если данные в диапазоне E3: E9 меньше 275 заказов на продажу.
Как и в случае с аргументом Criteria1, мы введем ссылку на ячейку для местоположения Criteria2 в диалоговое окно, а не на сами данные.
- Нажмите на строку Критерии2 в диалоговом окне.
- Нажмите на ячейку E12 , чтобы ввести ссылку на эту ячейку. Функция будет искать диапазон, выбранный на предыдущем шаге, для данных, которые соответствуют критериям.
- Нажмите ОК , чтобы завершить функцию SUMIFS и закрыть диалоговое окно.
Ответ ноль (0) появится в ячейке F12 (ячейка, в которую мы ввели функцию), потому что мы еще не добавили данные в поля Criteria1 и Criteria2 (C12 и D12). Пока мы этого не сделаем, функция не будет суммироваться, поэтому итоговое значение остается на нуле.
Добавление критериев поиска и завершение учебника
Последний шаг в учебнике – добавить данные в ячейки на листе, который определен как содержащий аргументы Criteria .
Функция СУММЕСЛИ (SUMIF) в Excel используется для суммирования значений, отвечающих заданным вами критериям.
Что возвращает функция
Возвращает сумму чисел, указанных в качестве аргументов и отвечающих заданным в формуле критериям.
Синтаксис
=SUMIF(range, criteria [sum_range]) — английская версия
=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]) — русская версия
Аргументы функции
- range (диапазон) — диапазон ячеек, по которым оцениваются критерии. Аргументом могут быть числа, текст, массивы или ссылки, содержащие числа;
- criteria(условие) — критерии, которые проверяются по указанному диапазону ячеек и определяют, какие ячейки суммировать;
- sum_range(диапазон_суммирования) — (не обязательно) суммируемые ячейки. Если этот аргумент не указан, то функция использует аргумент range (диапазон)в качестве sum_range (диапазон_суммирования).
Дополнительная информация
- суммирование ячеек в функции СУММЕСЛИ производится на основе одного критерия, если вам необходимо указать несколько критериев — используйте функцию SUMIFS (СУММЕСЛИМН) ;
- пустые и текстовые ячейки в аргументе sum_range (диапазон_суммирования) игнорируются;
- критерием для функции могут служить числа, выражения, результаты вычисления из других ячеек, текст или формула;
- если в качестве критерия указан текст или математический/логический символ (=,+,-,/,*), то такие значения должны быть указаны в двойных кавычках;
- аргумент с критерием не должен быть длиннее чем 255 символов;
- в качестве критерия могут использоваться подстановочные знаки (*);
- если количество ячеек в диапазоне критерия и аргументе sum_range (диапазон_суммирования) различаются, то размер диапазона критерия будет в приоритете.
Примеры использования функции СУММЕСЛИ в Excel
Привет,
Подскажите, пожалуйста, что делаю не так:
=SUMIF(‘Sheet1’!AC2:AC55, «>B2», ‘Sheet1’!Y2:Y55)
Сама формула на Sheet2, в Sheet1 столбце AC даты, в Sheet1 стобце Y суммы, в ячейке B2 =Today(). Необходимо сложить суммы соответсвующие датам позднее сегодняшнего дня. Расположение на разных листах принципиально, так как Sheet1 постоянно обновляется.
Заранее благодарю,
Евгения
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel Web App Excel 2010 Excel Starter 2010 More. Less
The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar value.
This video is part of a training course called Advanced IF functions.
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], . )
Argument name
Description
Sum_range (required)
The range of cells to sum.
Criteria_range1 (required)
The range that is tested using Criteria1.
Criteria_range1 and Criteria1 set up a search pair whereby a range is searched for specific criteria. Once items in the range are found, their corresponding values in Sum_range are added.
Criteria1 (required)
The criteria that defines which cells in Criteria_range1 will be added. For example, criteria can be entered as 32, ">32", B4, "apples", or "32".
Criteria_range2, criteria2, … (optional)
Additional ranges and their associated criteria. You can enter up to 127 range/criteria pairs.
Examples
To use these examples in Excel, drag to select the data in the table, right-click the selection, and pick Copy. In a new worksheet, right-click cell A1 and pick Match Destination Formatting under Paste Options.
Quantity Sold
Salesperson
Description
=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "Tom")
Adds the number of products that begin with A and were sold by Tom. It uses the wildcard character * in Criteria1, "=A*" to look for matching product names in Criteria_range1 B2:B9, and looks for the name "Tom" in Criteria_range2 C2:C9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 20.
=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, "Tom")
Adds the number of products that aren’t bananas and are sold by Tom. It excludes bananas by using <> in the Criteria1, "<>Bananas", and looks for the name "Tom" in Criteria_range2 C2:C9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 30.
Common Problems
Description
0 (Zero) is shown instead of the expected result.
Make sure Criteria1,2 are in quotation marks if you are testing for text values, like a person's name.
The result is incorrect when Sum_range has TRUE or FALSE values.
TRUE and FALSE values for Sum_range are evaluated differently, which may cause unexpected results when they're added.
Cells in Sum_range that contain TRUE evaluate to 1. Those that contain FALSE evaluate to 0 (zero).
Best practices
Description
Use wildcard characters.
Using wildcard characters like the question mark (?) and asterisk (*) in criteria1,2 can help you find matches that are similar but not exact.
A question mark matches any single character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) in front of the question mark.
For example, =SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "To?") will add all instances with name that begin with "To" and ends with a last letter that could vary.
Understand the difference between SUMIF and SUMIFS.
The order of arguments differ between SUMIFS and SUMIF. In particular, the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. This is a common source of problems using these functions.
If you're copying and editing these similar functions, make sure you put the arguments in the correct order.
Use the same number of rows and columns for range arguments.
The Criteria_range argument must contain the same number of rows and columns as the Sum_range argument.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;"> 5")
Это видео — часть учебного курса Сложение чисел в Excel.
При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; "Иван"; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны "Иван".
Если необходимо выполнить суммирование ячеек в соответствии с несколькими условиями, используйте функцию СУММЕСЛИМН.
Синтаксис
СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
Аргументы функции СУММЕСЛИ описаны ниже.
Диапазон — обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже).
Условие .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Можно включит под вопросицу (?), которая соответствует любому одиночному символу, звездочка (*) — любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак "тильда" (~).
Например, условия могут быть выражены как 32, ">32", B5, "3?", "яблок*", "*~?", или СЕГОДНЯ().
Важно: Все текстовые условия и условия с логическими и математическими знаками необходимо заключать в двойные кавычки ("). Если условием является число, использовать кавычки не требуется.
Диапазон_суммирования .Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).
Sum_range должны быть того же размера и фигуры, что идиапазон . В этом случае производительность может понести убытки, и формула суммирует диапазон ячеек, который начинается с первой ячейки в sum_range но имеет те же измерения, что и диапазон. Например:
Добавляет ячейки в диапазон, который соответствует нескольким критериям.
Синтаксис
выражение Переменная, представляюная объект WorksheetFunction .
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Arg1 | Обязательный | Range | Sum_range — диапазон суммы. |
Arg2 | Обязательный | Range | Criteria_range1, criteria_range2. - Один или несколько диапазонов, в которых можно оценить связанные критерии. |
Arg3 — Arg30 | Обязательный | Variant | Criteria1, criteria2. - Один или несколько критериев в виде числа, выражения, ссылки на ячейки или текста, определяя, какие ячейки будут добавлены. Например, критерии могут быть выражены как 32, "32", ">32", "яблоки" или B4. |
Возвращаемое значение
64-разрядное число с плавающей запятой двойной точности.
Примечания
Каждая ячейка в sum_range суммируется только в том случае, если все указанные соответствующие критерии соответствуют действительности для этой ячейки.
Ячейки в sum_range, содержащие TRUE, оцениваются как 1; ячейки в sum_range, содержащие FALSE, оцениваются как 0 (ноль).
В критериях используйте символы подпольной карты, знак вопросов (?) и звездочки (*). Знак вопроса совпадает с любым одним персонажем; звездочка соответствует любой последовательности символов. Если вы хотите найти фактический знак вопроса или звездочки, введите тильду (~) перед персонажем.
Каждый criteria_range не должен быть того же размера и формы, что и sum_range. Фактические ячейки, которые добавляются, определяются с помощью верхней левой ячейки в этой criteria_range в качестве первой ячейки, а затем в том числе ячеек, которые соответствуют размеру и форме для sum_range. Пример:
Если sum_range | И criteria_range | Фактические оцениваемые ячейки |
---|---|---|
A1:A5 | B1:B5 | B1:B5 |
A1:A5 | B1:B3 | B1:B5 |
A1:B4 | C1:D4 | C1:D4 |
A1:B4 | C1:C2 | C1:D4 |
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Читайте также: