Не работает формула sumifs

Исправление ошибки #ЗНАЧ! в функции СУММЕ ЕСЛИ или СУММЕСС

В этом разделе перечислены распространенные причины ошибки #ЗНАЧ! в функциях СУММЕСЛИ и СУММЕСЛИМН и способы их устранения.

Проблема: формула ссылается на ячейки в закрытой книге

Функции СУММЕ ЕСЛИ и СУММЕСС, которые ссылаются на ячейку или диапазон в закрытой книге, приведут к #VALUE! ошибку «#ВЫЧИС!».

Примечание. Это известная проблема с несколькими другими функциями Excel, такими как СЧЁТЕ ЕСЛИ, СЧЁТЕФМН и СЧИТАТЬBLANK. См. функции СУММЕ ЕСЛИ, СЧЁТЕФ и СЧИТАТЬ #VALUE. Ошибка дополнительные сведения.

Решение: Откройте книгу, указанную в формуле, и нажмите клавишу F9, чтобы обновить формулу.

Эту проблему также можно обойти, используя функции СУММ и ЕСЛИ вместе в формуле массива. См. сообщение об ошибке функций СУММЕФ, СЧЁТЕ ЕСЛИ и СЧИТАТЬ СЧИТАТЬ #VALUE! дополнительные сведения.

Проблема: длина строки условия превышает 255 символов

Функции СУММЕСЛИ и СУММЕСЛИМН возвращают неправильные результаты, если они используются для сопоставления строк длиннее 255 символов.

Решение: Сократите строку, если это возможно. Если сократить строку нельзя, используйте функцию СЦЕПИТЬ или оператор-амперсанд (&), чтобы разделить значение на несколько строк. Например:

=СУММЕСЛИ(B2:B12;»длинная строка»&»еще одна длинная строка»)

Проблема: в функции СУММЕСЛИМН аргумент диапазон_условия не совпадает с аргументом диапазон_суммирования.

Аргументы диапазонов в функции СУММЕСЛИМН должны совпадать. Это означает, что в аргументах диапазон_условия и диапазон_суммирования должно указываться одинаковое количество строк и столбцов.

В следующем примере формула должна возвращать сумму ежедневных продаж apples в Bellevue. Однако sum_range (C2:C10) не соответствует количеству строк и столбцов в аргументах criteria_range (A2:A12 & B2:B12). Использование синтаксиса =СУММЕ ЕСЛИМН( C2:C10, A2:A12,A14, B2:B12,B14) приведет к #VALUE! ошибку «#ВЫЧИС!».

Решение: В приведенном примере измените диапазон_суммирования на C2:C12 и выполните формулу повторно.

Примечание: В функции СУММЕСЛИ можно использовать диапазоны разного размера.

У вас есть вопрос об определенной функции?

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

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

Функция СУММЕСЛИМН (SUMIFS) в Excel используется для суммирования значений по нескольким критериям. Если вы хотите суммировать значения по одному критерию, то лучше использовать функцию SUMIF (СУММЕСЛИ).

Что возвращает функция

Возвращает сумму чисел по нескольким критериям.

Синтаксис

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, critertia2],…) – английская версия

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …) – русская версия

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

• sum_range (диапазон_суммирования) – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
• criteria_range1 (диапазон_условия1), сriteria1 (условие1) – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
• [criteria_range2], criteria2([диапазон_условия2], условие 2) – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;

Дополнительная информация

• Суммирование значений в функции производится на основе нескольких критериев;
• Пустые и текстовые ячейки игнорируются в аргументе sum_range (диапазон_суммирования);
• Критерием могут служить числа, выражения, ссылка на ячейку, текст или формула;
• Критерий, указанный в текстовом или логическом/математическом формате (=,+,-,/,*) должны быть указаны в двойных кавычках;
• В качестве критерия могут использоваться подстановочные знаки (*);
• Критерий не может быть больше чем 255 символов по длине;
• Ячейки в аргументе sum_range (диапазон_суммирования) суммируются только при выполнении всех условий, заданных в формуле;
• Если среди значений аргумента sum_range (диапазон_суммирования) указано логическое значение TRUE или FALSE, то они подразумевают значения “1” и “0” соответственно;
• Диапазон аргументов sum_range (диапазон_суммирования) и всех критериев должны совпадать.

Примеры использования функции СУММЕСЛИМН в Excel

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Источник

Функция SUMIF (СУММЕСЛИ) в Excel. Как использовать?

Функция СУММЕСЛИ (SUMIF) в Excel используется для суммирования значений, отвечающих заданным вами критериям.

Что возвращает функция

Возвращает сумму чисел, указанных в качестве аргументов и отвечающих заданным в формуле критериям.

Синтаксис

=SUMIF(range, criteria [sum_range]) – английская версия

=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]) – русская версия

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

• range (диапазон) – диапазон ячеек, по которым оцениваются критерии. Аргументом могут быть числа, текст, массивы или ссылки, содержащие числа;
• criteria(условие) – критерии, которые проверяются по указанному диапазону ячеек и определяют, какие ячейки суммировать;
• sum_range(диапазон_суммирования) – (не обязательно) суммируемые ячейки. Если этот аргумент не указан, то функция использует аргумент range (диапазон)в качестве sum_range (диапазон_суммирования).

Дополнительная информация

• суммирование ячеек в функции СУММЕСЛИ производится на основе одного критерия, если вам необходимо указать несколько критериев – используйте функцию SUMIFS (СУММЕСЛИМН) ;
• пустые и текстовые ячейки в аргументе sum_range (диапазон_суммирования) игнорируются;
• критерием для функции могут служить числа, выражения, результаты вычисления из других ячеек, текст или формула;
• если в качестве критерия указан текст или математический/логический символ (=,+,-,/,*), то такие значения должны быть указаны в двойных кавычках;
• аргумент с критерием не должен быть длиннее чем 255 символов;
• в качестве критерия могут использоваться подстановочные знаки (*);
• если количество ячеек в диапазоне критерия и аргументе sum_range (диапазон_суммирования) различаются, то размер диапазона критерия будет в приоритете.

Примеры использования функции СУММЕСЛИ в Excel

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Источник

Cоветы и руководства по Google Таблицам

Мастер Google Таблиц с полезными советами и руководствами

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

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

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

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

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

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

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

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

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

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

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

• sum_range — это диапазон ячеек, содержащий значения, которые вы хотите проверить.
• criteria_range1 является диапазон для проверки факторам1.
• criteria 1 — это условие, которому должен удовлетворять диапазон_критериев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 проверила каждую ячейку от B2 до B9 и от C2 до C9, чтобы найти ячейки, которые удовлетворяют обоим условиям — «Производство» и «Нью-Йорк» соответственно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник