Может кто-нибудь объяснить, пожалуйста, эту ошибку? Это не имеет никакого смысла для меня.
До сих пор я знаю, что это прекрасно работает, если у меня нет «» в моем «Критерии». Я перепробовал каждый escape-символ, который мог придумать, а также просматривал Google для решения, но нет воспользоваться .
Чтобы сделать вещи еще лучше, тот же escape-символ, который я использую ниже, работает с SUMIF, но, очевидно, не с SUMIF S , который настолько глуп, что я не могу поверить, что это проблема. там должно быть что-то, что я скучаю.
< Сильный > формула:
=SUMIFS(T:T,S:S,"=10"" Tab",U:U,"<>GSM") + SUMIFS(Y:Y, "=10"" Tablet", Z:Z,"<>GSM")
Ошибка:
«SUMIFS ожидает, что все аргументы после позиции 3 будут в парах».
2 ответа
Лучший ответ
Ошибка вызвана вашей формулой 2-го суммирования. скорее всего это должно быть:
=SUMIFS(T:T, S:S, "=10"" Tab", U:U, "<>GSM")+
SUMIFS(T:T, Y:Y, "=10"" Tablet", Z:Z, "<>GSM")
0
player0
18 Мар 2020 в 13:32
Ошибка появляется, потому что после того, как вы ввели summing range, вы должны ввести другие входные аргументы парами. Как 1-й критерий диапазона с 1-м критерием выражения, 2-й критерий диапазона с 2-м критерием выражения
Если хотите, можете ознакомиться с документацией.
Например:
В вашем случае ваша формула чувствует себя немного не так, потому что у вас нет нужного количества параметров.
В этой части у вас даже нет критериев для диапазона.
SUMIFS(Y:Y, "=10"" Tablet", Z:Z,"<>GSM")
Если диапазон суммирования и критерии совпадают, вам нужно сделать это явно.
0
Raserhin
18 Мар 2020 в 11:27
I’m trying to reference a cell depending on the value of A2, but when I use the following code, I get an #N/A error. My google is not in English, so I don’t know the exact translation, but it’s something about the IFS function wanting all arguments after position 0 to come in pairs.
This is what the code looks like currently.
=IFS(
$A2="[Greater Flask of the Vast Horizon]"; G2;
$A2="[Greater Flask of the Undertow]"; G3;
$A2="[Greater Flask of Endless Fathoms]"; G4;
$A2="[Greater Flask of the Currents]"; G5;
$A2="[Potion of Empowered Proximity]"; G6;
$A2="[Potion of Focused Resolve]"; G7;
$A2="[Superior Battle Potion of Intellect]"; G8;
$A2="[Superior Battle Potion of Agility]"; G9;
$A2="[Superior Battle Potion of Strength]"; G10;
$A2="[Superior Battle Potion of Stamina]"; G11;
$A2="[Potion of Unbridled Fury]"; G12;
$A2="[Superior Steelskin Potion]"; G13;
$A2="[Potion of Wild Mending]"; G14;
$A2="[Abyssal Healing Potion]"; G15;
)
What should I change to make it work like intended?
player0
121k9 gold badges60 silver badges113 bronze badges
asked Dec 28, 2019 at 21:59
remove the comma/semicolon after G15:
=IFS(
$A2="[Greater Flask of the Vast Horizon]"; G2;
$A2="[Greater Flask of the Undertow]"; G3;
$A2="[Greater Flask of Endless Fathoms]"; G4;
$A2="[Greater Flask of the Currents]"; G5;
$A2="[Potion of Empowered Proximity]"; G6;
$A2="[Potion of Focused Resolve]"; G7;
$A2="[Superior Battle Potion of Intellect]"; G8;
$A2="[Superior Battle Potion of Agility]"; G9;
$A2="[Superior Battle Potion of Strength]"; G10;
$A2="[Superior Battle Potion of Stamina]"; G11;
$A2="[Potion of Unbridled Fury]"; G12;
$A2="[Superior Steelskin Potion]"; G13;
$A2="[Potion of Wild Mending]"; G14;
$A2="[Abyssal Healing Potion]"; G15)
answered Dec 28, 2019 at 22:09
player0player0
121k9 gold badges60 silver badges113 bronze badges
1
I’m trying to reference a cell depending on the value of A2, but when I use the following code, I get an #N/A error. My google is not in English, so I don’t know the exact translation, but it’s something about the IFS function wanting all arguments after position 0 to come in pairs.
This is what the code looks like currently.
=IFS(
$A2="[Greater Flask of the Vast Horizon]"; G2;
$A2="[Greater Flask of the Undertow]"; G3;
$A2="[Greater Flask of Endless Fathoms]"; G4;
$A2="[Greater Flask of the Currents]"; G5;
$A2="[Potion of Empowered Proximity]"; G6;
$A2="[Potion of Focused Resolve]"; G7;
$A2="[Superior Battle Potion of Intellect]"; G8;
$A2="[Superior Battle Potion of Agility]"; G9;
$A2="[Superior Battle Potion of Strength]"; G10;
$A2="[Superior Battle Potion of Stamina]"; G11;
$A2="[Potion of Unbridled Fury]"; G12;
$A2="[Superior Steelskin Potion]"; G13;
$A2="[Potion of Wild Mending]"; G14;
$A2="[Abyssal Healing Potion]"; G15;
)
What should I change to make it work like intended?
player0
121k9 gold badges60 silver badges113 bronze badges
asked Dec 28, 2019 at 21:59
remove the comma/semicolon after G15:
=IFS(
$A2="[Greater Flask of the Vast Horizon]"; G2;
$A2="[Greater Flask of the Undertow]"; G3;
$A2="[Greater Flask of Endless Fathoms]"; G4;
$A2="[Greater Flask of the Currents]"; G5;
$A2="[Potion of Empowered Proximity]"; G6;
$A2="[Potion of Focused Resolve]"; G7;
$A2="[Superior Battle Potion of Intellect]"; G8;
$A2="[Superior Battle Potion of Agility]"; G9;
$A2="[Superior Battle Potion of Strength]"; G10;
$A2="[Superior Battle Potion of Stamina]"; G11;
$A2="[Potion of Unbridled Fury]"; G12;
$A2="[Superior Steelskin Potion]"; G13;
$A2="[Potion of Wild Mending]"; G14;
$A2="[Abyssal Healing Potion]"; G15)
answered Dec 28, 2019 at 22:09
player0player0
121k9 gold badges60 silver badges113 bronze badges
1
Функция СУММЕСЛИМН была добавлена в 2007-й версии Excel в дополнение к функции СУММЕСЛИ и она суммирует ячейки в диапазоне, удовлетворяющие нескольким условиям.
Описание функции СУММЕСЛИМН
Суммирует ячейки в диапазоне, удовлетворяющие нескольким условиям. Например, если необходимо суммировать числа в диапазоне
A1:A20, которым соответствуют значения в диапазоне B1:B20 больше нуля (0) и значения в диапазоне C1:C20 меньше 10, можно использовать следующую формулу:
=СУММЕСЛИМН(A1:A20; B1:B20; ">0"; C1:C20; "<10")Важно! Порядок аргументов в функциях СУММЕСЛИМН и СУММЕСЛИ различается. В СУММЕСЛИМН аргумент диапазон_суммирования является первым аргументом, а в СУММЕСЛИ — третьим. При копировании и изменении этих похожих функций необходимо следить за тем, чтобы аргументы были указаны в правильном порядке.
Синтаксис
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условий1; условия1; [диапазон_условий2; условия2]; ...)Аргументы
диапазон_суммированиядиапазон_условий1условия1диапазон_условий2; условия2 …
Обязательный аргумент. Одна или несколько ячеек для суммирования, включая числа или имена, диапазоны или ссылки на ячейки. Пустые ячейки и ячейки, содержащие текстовые значения, пропускаются.
Обязательный аргумент. Первый диапазон, в котором проверяется соответствующее условие.
Обязательный аргумент. Условия в виде числа, выражения, ссылки на ячейку или в виде текста, определяющие, какие ячейки в аргументе диапазон_критериев1 будут просуммированы. Например, условия могут быть представлены в следующем виде: 32, «>32», B4, «яблоки» или «32».
Необязательный аргумент. Дополнительные диапазоны и условия для них. Разрешается использовать до 127 пар диапазонов и условий.
Замечания
- Каждая ячейка в аргументе диапазон_суммирования суммируется только в том случае, если выполнены все указанные условия, соответствующие этой ячейке. Например, формула содержит два аргумента диапазон_условий. Если первая ячейка аргумента диапазон_условий1 соответствует аргументу условия1, а первая ячейка аргумента диапазон_условий2 — аргументу условия2, первая ячейка аргумента диапазон_суммирования добавляется к сумме (и т. д. для всех остальных ячеек в указанных диапазонах).
- Ячейки в аргументе диапазон_суммирования, которым присвоено значение ИСТИНА, оцениваются как 1; ячейки в аргументе диапазон_суммирования, которым присвоено значение ЛОЖЬ, оцениваются как 0 (нуль).
- В отличие от аргументов диапазона и условий в функции СУММЕСЛИ, в функции СУММЕСЛИМН каждый аргумент диапазон_условий обязательно должен иметь то же количество строк и столбцов, что и аргумент диапазон_суммирования.
- В условии можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*) . Вопросительный знак соответствует одному любому символу, а звездочка — любой последовательности знаков. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (~).
Пример
Если вы хотите найти сумму значений на основе условия, вы, скорее всего, знаете о функциях SUMIF и SUMIFS в Google Таблицах. Проблема с функцией SUMIF в том, что она позволяет указать только одно условие.
Однако во многих случаях условия не такие уж и сложные. Нам часто нужно найти суммы, основанные более чем на одном условии. В таких случаях функция SUMIFS может оказаться весьма удобной.
В этом руководстве я покажу вам, как использовать функцию SUMIFS в Google Таблицах, используя несколько практических примеров.
Проще говоря, функция СУММЕСЛИМН находит сумму значений, удовлетворяющих более чем одному условию.
Функция просматривает диапазон ячеек и извлекает те, которые соответствуют всем указанным условиям.
После сканирования диапазона он суммирует полученные значения и отображает результат.
В качестве примера: если у вас есть список транзакций продаж и вы хотите узнать сумму всех транзакций в определенном диапазоне дат, вы можете сделать это с помощью СУММЕСЛИМН.
Синтаксис функции СУММЕСЛИМН
Общий синтаксис функции 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 в свои электронные таблицы, когда вам нужно найти сумму на основе более чем одного условия.
Надеюсь, этот урок был Вам полезен.
Skip to content
В этом руководстве объясняется различие между функциями СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) с точки зрения их синтаксиса и использования, а также приводятся примеры формул для суммирования значений с несколькими критериями в Excel 2016, 2013, 2010, 2007, 2003 и ниже.
Как известно, Microsoft Excel предоставляет множество функций для выполнения различных расчетов с данными. Мы уже рассмотрели СУММЕСЛИ, которая суммирует числа, соответствующие указанным критериям. Теперь пришло время перейти к расширенной версии этой функции СУММЕСЛИМН, которая позволяет найти сумму по нескольким условиям.
Те, кто знаком с функцией СУММЕСЛИ, могут подумать, что преобразование ее в СУММЕСЛИМН потребует лишь букв «МН» и некоторого количества дополнительных критериев. Это может показаться вполне логичным… но «логично» — это не всегда имеет место при работе с Microsoft:)
Как работает СУММЕСЛИМН?
При помощи СУММЕСЛИМН можно найти сумму величин, для которых есть много условий. Она появилась впервые в MS Excel 2007, поэтому вы можете использовать ее во всех современных версиях программы.
По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:
СУММЕСЛИМН(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2],…)
Первые 3 аргумента являются обязательными, а вот дополнительные диапазоны и связанные с ними условия являются необязательными.
диапазон_суммирования — требуется одна или более ячеек для суммирования. Это может быть отдельная клеточка, область или именованный диапазон. Суммируются только ячейки с чисами; пустые и текстовые значения игнорируются.
диапазон_условия1 — обязательный первый диапазон, который должен быть оценен по соответствующим критериям.
условие1— обязательное первое условие, которое должно быть выполнено. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, «> = 10», A1, «яблоко» или СЕГОДНЯ().
Все, что следует далее — это дополнительные диапазоны и связанные с ними критерии. Они не являются обязательными, но если у вас только одно ограничение, то зачем вам эта функция? Просто используйте СУММЕСЛИ. Тем не менее, вы можете использовать до 127 пар диапазон/условие.
Важно! Функция СУММЕСЛИМН работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным критериям (все требования соблюдаются для этой ячейки).
Использование СУММЕСЛИМН и СУММЕСЛИ в Excel — что нужно запомнить?
Поскольку целью этого руководства является охват всех возможных способов суммирования значений по большому количеству ограничений, мы обсудим примеры выражений с обеими функциями — СУММЕСЛИМН и СУММЕСЛИ с несколькими критериями. Чтобы использовать их правильно, вам необходимо четко понимать, что общего между этими двумя функциями и чем они отличаются.
Хотя общая часть ясна — схожий пункт назначения и параметры – но различия не так очевидны, хотя и достаточно существенны.
1. Порядок аргументов
Аргументы применяются по-разному. В частности, диапазон_сумирования является 1-м параметром в СУММЕСЛИ, но является третьим в СУММЕСЛИМН.
На первый взгляд может показаться, что Microsoft намеренно усложняет процесс обучения для своих пользователей. Однако при ближайшем рассмотрении вы увидите причины этого. Дело в том, что этот диапазон является необязательным в СУММЕСЛИ. Если вы его опустите, то нет никаких проблем, ваша формула будет складывать в диапазоне поиска (первый параметр).
В СУММЕСЛИМН он, напротив, очень важен и обязателен, и поэтому и стоит первым. Вероятно, ребята из Microsoft подумали, что после добавления 10- й или 100- й пары диапазон/критерий кто-то может забыть указать диапазон для суммирования:)
2. Диапазон суммирования и область критериев должны быть одинакового размера
В функции СУММЕСЛИ эти аргументы не обязательно должны иметь одинаковую размерность. Достаточно указать начальную точку. В СУММЕСЛИМН они должны содержать одинаковое количество строк и столбцов.
Выражение =СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F22;I3) вернет сообщение об ошибке #ЗНАЧ!, так как второй параметр поиска (F2:F22) не совпадает по размеру с остальными (E2:E21) и (C2:C21).
Хорошо, хватит стратегии (т.е. теории), давайте перейдем к тактике (к примерам).
Суммирование с множеством условий.
Имеются данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».
Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.
=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I3)
В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.
В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.
Если ОБА эти требования выполняются, то такой заказ нам подходит, и его стоимость мы учтём.
Как видите, у нас найдено 2 совпадения, в которых был продан молочный шоколад.
Использование операторов сравнения.
Рассчитаем по покупателю «Красный» стоимость заказов, в которых было более 100 единиц товара. Как видим, здесь нужно использовать и текстовый, и числовой критерий.
Критерии можно записать в саму формулу, и выглядеть это будет так:
=СУММЕСЛИМН(E2:E21;B2:B21;”Красный”;D2:D21;”>100”)
Но более рационально использовать ссылки, как это и сделано на рисунке:
=СУММЕСЛИМН(E2:E21;B2:B21;I2;D2:D21;I4)
Примечание. Обратите внимание, что в формулах логические выражения с операторами сравнения всегда должны быть заключены в двойные кавычки («»).
Синтаксис, а также работа с числами, текстом и датами у этой функции точно такие же, как и СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу о условном суммировании.
А как еще можно решить нашу задачу?
Способ 2. Используем функцию СУММПРОИЗВ.
Разберем подробнее, как работает СУММПРОИЗВ():
=СУММПРОИЗВ(—(B2:B21=$I$12);—(D2:D21>I13);E2:E21)
Результатом вычисления B2:B21=$I$12 является массив
{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}.
ИСТИНА означает соответствие кода покупателя условию, т.е. слову Красный. Массив этот можно увидеть, выделив в строке формул B2:B21=$I$12, а затем нажав F9.
А что за странные знаки «минус» перед этими выражениями? Дело в том, что нам необходимы не эти логические выражения, а числа, чтобы их затем можно было перемножать и складывать. Если Эксель производит математическую операцию с логическим выражением, то он автоматически преобразует его в число. А знак минус означает умножение на -1. А если дважды умножить на -1, то число в результате не изменится. Это мы помним еще из школьной математики 😊
И в результате логический массив превратится в массив чисел {0:1:0:0:0:0:0:0:0:0:0:0:1:0:0:0:1:0:0:0}.
Результатом вычисления D2:D21>I13 является массив
{ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}.
ИСТИНА соответствует ограничению «количество больше 100». Здесь мы также применяем двойное отрицание, чтобы преобразовать логические переменные в числа.
И, наконец, результатом вычисления В2:В13 является массив {11250:23210:12960:3150:5280:9750:3690:18300:5720:6150: 8400:2160:7200:1890:17050:3450:15840:2250:7200:8250}, т.е. просто числа из столбца E.
Результатом поэлементного умножения этих трех массивов является {0:23210:0:0:0:0:0:0:0:0:0:0:0:0:0:0:15840:0:0:0}. Суммируем эти произведения и получаем 39050.
Способ 3. Формула массива.
И еще один вариант расчета – применим формулу массива. В I14 запишем:
=СУММ((B2:B21=I12)*(D2:D21>I13)*(E2:E21))
Не забудьте в конце нажать комбинацию клавиш CTRL+SHIFT+ENTER, чтобы обозначить это выражение как формулу массива. Фигурные скобки в начале и в конце программа добавит автоматически. Вновь получим результат 39050.
Способ 4. Автофильтр.
Еще один альтернативный вариант – применение автофильтра. Для этого преобразуйте диапазон данных A1:F21 в «умную» таблицу. Напомню, что для этого в меню «Главная» выберите «Форматировать как таблицу». После этого добавьте в нее строку итогов (вкладка «Конструктор») и установите необходимые фильтры.
Без всяких формул итог по отфильтрованным строкам будет определён.
Как СУММЕСЛИМН работает с датами?
Если вы хотите отобрать и сложить какие-то показатели в определенном временном интервале на основе текущей даты, используйте функцию СЕГОДНЯ() в ваших ограничениях, как это показано ниже.
Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце А попадает в последние 7 дней, включая сегодняшний день (предполагается, что сегодня 7 февраля):
=СУММЕСЛИМН(D2:D21;A2:A21;»<=»&СЕГОДНЯ();A2:A21;»>=»&СЕГОДНЯ()-6)
Замечание. Когда вы при составлении ограничения используете другую функцию Excel вместе с логическим оператором, нужно использовать амперсанд (&) для объединения всего выражения в виде текста, например «<=»&СЕГОДНЯ().
Аналогичным образом вы можете использовать функцию Excel СУММЕСЛИ для суммирования каких-то показателей в заданном диапазоне дат. Например, следующая формула также решит нашу задачу:
=СУММЕСЛИ(A2:A21;»>=»&СЕГОДНЯ()-6;D2:D21) — СУММЕСЛИ(A2:A21;»<=»&СЕГОДНЯ();D2:D21)
Однако СУММЕСЛИМН сложение делает гораздо проще и понятнее, не так ли?
Суммирование по пустым и непустым ячейкам.
При анализе отчетов и других данных вам часто может потребоваться суммировать данные, соответствующие пустым или непустым клеткам таблицы.
| Критерии | Описание | Содержание | |
| Пустые ячейки | «=» | Суммируйте числа, соответствующие пустым, которые не содержат абсолютно ничего — ни формулы, ни строки нулевой длины. | =СУММЕСЛИМН(C2:C10;A2:A10;»=»;B2:B10,»=») Суммируйте в C2:C10, если соответствующие ячейки в столбцах A и B абсолютно пусты. |
| «» | Суммируйте числа, соответствующие «визуально» пустым, включая те, которые содержат пустые строки, возвращаемые какой-либо другой функцией Excel (например, ячейки с формулой вроде = «»). | =СУММЕСЛИМН(C2:C10;A2:A10;»»;B2:B10,»») Суммируйте в C2:C10 с теми же параметрами, что и в приведенной выше формуле, но с пустыми строками. |
|
| Непустые ячейки | «<>» | Суммируйте числа, соответствующие непустым, включая строки нулевой длины. | =СУММЕСЛИМН(C2:C10;A2:A10;»<>»;B2:B10,»<>») Суммируйте в C2: C10, если соответствующие ячейки в столбцах A и B не пусты, включая ячейки с пустыми строками. |
| Суммируйте числа, соответствующие непустым, не включая строки нулевой длины. | =СУММ(C2:C10) — СУММЕСЛИМН(C2:C10;A2:A10;»»;B2:B10,»») или {=СУММ((C2:C10)*(ДЛСТР(A2:A10)>0)*(ДЛСТР(B2: B10)>0))} Если в столбцах A и B содержится текст ненулевой длины, тогда соответствующее число из C складывается. Внимание! Это формула массива! Фигурные скобки вводить не нужно! |
А теперь давайте посмотрим, как вы можете использовать формулу СУММЕСЛИМН с «пустыми» и «непустыми» ячейками для реальных данных.
По покупателю «Красный» рассчитаем количество товара в невыполненных заказах. Для этого в столбце B ищем соответствующее название клиента, а в F – пустую ячейку. Если оба требования совпадают, складываем количество товара из столбца D.
=СУММЕСЛИМН(D2:D21;F2:F21;»»;B2:B21;»Красный»)
или
=СУММЕСЛИМН(D2:D21;F2:F21;»=»;B2:B21;»Красный»)
Каждое из этих выражений дает верный результат – 144 единицы в заказе от 4 февраля.
Сумма нескольких условий.
А теперь давайте рассчитаем общую стоимость выполненных заказов по двум товарам.
Если мы просто добавим второй критерий в I3, и вместо I2 используем область I2:I3, то расчет будет неверным, поскольку в C2:C21 будем искать товар, в названии которого есть И «черный», И «молочный» одновременно. Ведь таких просто нет.
Поэтому первый вариант расчета таков:
=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I4)+СУММЕСЛИМН(E2:E21;C2:C21;I3;F2:F21;I4)
Просто складываем выполненные заказы сначала по первому, а затем по второму наименованию.
Второй вариант: используем элемент массива критериев и функцию СУММ.
=СУММ(СУММЕСЛИМН(E2:E21;C2:C21;{«*молочный*»;»*черный*»};F2:F21;I4))
Как видите, результаты получены одинаковые. Выбирайте способ, который будет для вас проще и понятнее.
Ещё примеры расчета суммы: