что значит суммесли в excel
7 примеров использования формулы СУММЕСЛИ в Excel с несколькими условиями
В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif) в таблицах Excel. Начнем с самых простых случаев, как можно использовать при этом знаки подстановки, назначить диапазон суммирования, работать с числами, текстом и датами. Особо остановимся на том, как использовать сразу несколько условий. И, конечно, мы применим новые знания на практике, рассмотрев несложные примеры.
Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel, с 2016 по 2003 год. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.
Как пользоваться СУММЕСЛИ в Excel – синтаксис
Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.
Синтаксис функции выглядит следующим образом:
=СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования])
Диапазон – это область, которую мы исследуем на соответствие определённому значению.
Критерий – это значение или шаблон, по которому мы производим отбор чисел для суммирования.
Значение критерия может быть записано прямо в самой формуле. В этом случае не забывайте, что текст нужно обязательно заключать в двойные кавычки.
Также он может быть представлен в виде ссылки на ячейку таблицы, в которой будет указано требуемое ограничение. Безусловно, второй способ является более рациональным, поскольку позволяет гибко менять расчеты, не редактируя выражение.
Итак, если он указан, то расчет идет именно по его данным. Если отсутствует, то складываются значения из той же области, где производился поиск.
Чтобы лучше понять это описание, рассмотрим несколько простых задач. Надеюсь, что они будут понятны не только «продвинутым» пользователям, но и подойдут для «чайников».
Примеры использования функции СУММЕСЛИ в Excel
Сумма если больше чем, меньше, или равно
Начнем с самого простого. Предположим, у нас есть данные о продажах шоколада. Рассчитаем различные варианты продаж.
D2:D21 – это координаты, в которых мы ищем значение.
I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.
Поскольку третий параметр функции не указан, то мы сразу складываем отобранные числа. Область поиска будет одновременно являться и диапазоном суммирования.
Можно указать его прямо в формуле, как это сделано в I13
=СУММЕСЛИ(D2:D21;» 
В I3 запишем выражение:
F2:F21 – это область, в которой мы отбираем подходящие значения.
I2 – здесь записано, что именно отбираем.
E2:E21 – складываем числа, соответствующие найденным совпадениям.
Конечно, можно указать параметр отбора прямо в выражении:
Но мы уже договорились, что так делать не совсем рационально.
Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.
Подстановочные знаки для частичного совпадения.
При работе с текстовыми данными часто приходится производить поиск по какой-то части слова или фразы.
Вернемся к нашему случаю. Определим, сколько всего было заказов на черный шоколад. В результате, у нас есть 2 подходящих наименования товара. Как учесть их оба? Для этого есть понятие неточного соответствия.
Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.
Символ “?” позволяет заменить собой один любой символ.
Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).
Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –
=СУММЕСЛИ(C2:C21;»*»&I2&»*»;E2:E21)
где * вставлены прямо в выражение и «склеены» с нужным текстом.
Этому требованию соответствует “Нет”.
Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.
) нужно поставить перед каждым из них. К примеру, если мы будем искать текст, состоящий из трех звездочек, то можено записать так:
А если текст просто содержит в себе 3 звездочки, то можно наше выражение переписать так:
Точная дата либо диапазон дат.
Если нам нужно найти сумму чисел, соответствующих определённой дате, то проще всего в качестве критерия указать саму эту дату.
Примечание. При этом не забывайте, что формат указанной вами даты должен соответствовать региональным настройкам вашей таблицы!
Обратите внимание, что мы также можем здесь вписать ее прямо в формулу, а можем использовать ссылку.
Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.
Рассчитаем за вчерашний день.
=СУММЕСЛИ(A2:A21;СЕГОДНЯ()-1;E2:E21)
СЕГОДНЯ()-1 как раз и будет «вчера».
Складываем за даты, которые предшествовали 1 февраля.
А если нас интересует временной интервал «от-до»?
Мы можем рассчитать итоги за определённый период времени. Для этого применим маленькую хитрость: разность функций СУММЕСЛИ. Предположим, нам нужна выручка с 1 по 4 февраля включительно. Из продаж после 1 февраля вычитаем все, что реализовано до 4 февраля.
Если критерий указать просто “*”, то мы учитываем для подсчета непустые ячейки, в которых имеется хотя бы одна буква или символ (кроме пустых).
Теперь рассмотрим, как можно находить сумму, соответствующую пустым ячейкам.
Для того, чтобы найти пустые, в которых нет ни букв, ни цифр, в качестве критерия поставьте парные одинарные кавычки ‘’, если значение критерия указано в ячейке, а формула ссылается на неё.
Если же указать на отбор только пустых ячеек в самой формуле, то впишите двойные кавычки.
=СУММЕСЛИ(F2:F21;«»;E2:E21)
Сумма по нескольким условиям.
Функция СУММЕСЛИ может работать только с одним условием, как мы это делали ранее. Но очень часто случается, что нужно найти совокупность данных, удовлетворяющих сразу нескольким требованиям. Сделать это можно как при помощи некоторых хитростей, так и с использованием других функций. Рассмотрим все по порядку.
Вновь вернемся к нашему случаю с заказами. Рассмотрим два условия и найдем, сколько всего сделано заказов черного и молочного шоколада.
1. СУММЕСЛИ + СУММЕСЛИ
Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).
Это самое простое решение, но не самое универсальное и далеко не единственное.
2. СУММ и СУММЕСЛИ с аргументами массива.
Вышеупомянутое решение очень простое и может выполнить работу быстро, когда критериев немного. Но если вы захотите работать с несколькими, то она станет просто огромной. В этом случае лучшим подходом является использование в качестве аргумента массива критериев. Давайте рассмотрим этот подход.
Вы можете начать с перечисления всех ваших условий, разделенных запятыми, а затем заключить итоговый список, разделенный точкой с запятой, в <фигурные скобки>, который технически называется массивом.
Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:
Поскольку здесь использован массив критериев, то результатом вычислений также будет массив, состоящий из двух значений.
А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.
=СУММ(СУММЕСЛИ($C$2:$C$21; ;$E$2:$E$21))
Важно, что результаты вычислений в первом и втором случае совпадают.
3. СУММПРОИЗВ и СУММЕСЛИ.
А если вы предпочитаете перечислять критерии в какой-то специально отведенной для этого части таблицы? Можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в заданных массивах и возвращает сумму этих произведений.
Вот как это будет выглядеть:
в H3 и H4 мы запишем критерии отбора.
Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21; ;E2:E21))
Результат, возвращаемый в обоих случаях, будет идентичен тому, что вы наблюдаете на скриншоте.
Важное замечание! Обратите внимание, что все перечисленные выше три способа производят расчет по логическому ИЛИ. То есть, нам нужны продажи шоколада, который будет или черным, или молочным.
Почему СУММЕСЛИ у меня не работает?
Этому может быть несколько причин. Иногда ваше выражение не возвращает того, что вы ожидаете, только потому, что тип данных в ячейке или в каком-либо аргументе не подходит для нее. Итак, вот что нужно проверить.
1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.
Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив <1,2,3>, Excel выдаст сообщение об ошибке.
Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)
Неверно : =СУММЕСЛИ(<1,2,3>, «цвет», C1:C3)
2. Ошибка при суммировании значений из других листов или рабочих книг
Как и любая другая функция Excel, СУММЕСЛИ может ссылаться на другие листы и рабочие книги, если они в данный момент открыты.
Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:
Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.
3. Чтобы избежать проблем, убедитесь, что диапазоны данных и поиска имеют одинаковый размер.
Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel 2010 и Excel 2016 сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.
Excel-plus
Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel
Функция СУММЕСЛИ в MS Excel. Варианты данной функции с описанием и примерами.
Функция СУММЕСЛИ в MS Excel. Варианты данной функции с описанием и примерами.
Функция СУММЕСЛИ Excel.
Ниже приведены варианты функции СУММЕСЛИ Excel. Описания, пошаговый алгоритм использования в зависимости от варианта функции, с примерами использования и иллюстрациями.
СУММЕСЛИ следует использовать в тех случаях, когда Вам необходимо суммировать значения в диапазоне, которые соответствует указанному Вами критерию.
Возможные варианты данной функции:
Отдельная статья описывает функцию СУММЕСЛИ Excel. Точное совпадение. Частичное совпадение. С текстовыми критерий. Содержит описание, примеры и иллюстрации. Статья находиться по этой ссылке: Функция СУММЕСЛИ в MS Excel. Точное совпадение. Частичное совпадение. Инструкция и примеры.
Самый простой и понятный способ вызвать данную функцию, это нажать в правом верхнем углу, рядом с кнопкой ∑, стрелку вниз (Рис. 1).
Появиться список функций. Как правило, это наиболее часто используемые функции. В этом списке уже может быть нужная Вам функция СУММЕСЛИ. В нашем случае ее там нет. Тогда выбираем пункт Другие функции. Он находиться внизу списка (Рис. 2).
Появляется диалоговое окно – Вставка функции. Самое верхнее, это поле для поиска нужной функции. Вводим название функции, выбираем ее в результатах поиска и нажимаем ОК (Рис. 3).
Появляется диалоговое окно — Аргументы функции (Рис. 4).
В нем есть три ключевых поля для заполнения:
Что бы выбрать нужный диапазон нажимаете на стрелочку в конце строки диапазона и выбираете нужные Вам ячейки с помощью мыши. Можно сразу выбрать нужный диапазон, не нажимая на стрелочку, но сначала нужно поставив курсор на нужную строку в диалоговом окне. Можно прописать диапазон вручную. Результат от этого не зависит. После того как все готово, нажимает ОК (Рис. 5).
Далее рассмотрим варианты использования данной функции на конкретных примерах, что бы понять, как они работают.
Напоминаю, что перед тем, как вызывать функцию, нужно выбрать ячейку, в которой должно появиться искомое значение, результат применения функции.
Сумма, если больше, Сумма, если меньше. СУММЕСЛИ.
Знаки, которые используются: >, 500»)
Напротив каждого примера стоит число от 100 до 1300. Нам нужно просуммировать, только значения, которые больше числа 500.
Вызываем функцию, как описано выше и выбираем нужный нам диапазон в строке – Диапазон (Е3;Е27). Это столбец со значениями чисел от 100 до 1300.
Вводим критерий. В строке Критерий, пишем: >500, вручную. Можно скопировать значение критерия с самой таблицы и добавить в ручную только знак >. Кавычки появятся автоматически (Рис. 7).
Нажимаем ОК. Напротив ячейки с словом ИТОГО, появилась сумма чисел больше 500 (Рис. 8)
Сумма, если меньше.
Формула в ячейке, как пример, выглядит вот так:
Сумма, если равно.
Формулы в ячейках, как пример, выглядят вот так:
В столбце D, есть четыре ячейки с Пример 1 (выделенные красным шрифтом). Наша задача суммировать значения, которые стоят только напротив Пример 1. Вызываем функцию СУММЕСЛИ. В Диапазоне выбираем нужный диапазон (столбец D) (D3:D27).
В Критерий, вручную пишем Пример 1 или копируем с таблицы. По этому критерию будет осуществляться поиск нужных ячеек в диапазоне (D3:D27).
В Диапазон_суммирования выбираем диапазон ячеек с значениями (столбец Е), которые будут суммироваться (E3:E27). Суммироваться будут те ячейки, которые будут отвечать нашему критерию и находиться напротив ячеек с значением Пример 1. Нажимаем ОК (Рис. 9).
Так же можно использовать данный вариант функции, когда из списка значений (цифр), нужно суммировать только цифры равные определенному значению. Например, в диапазоне значений (E3:E27), нам нужно суммировать только значения равные числу 100 (выделенные красным шрифтом). В строке Критерий указываем необходимое значений, после знака =. В нашем случае это цифра 100. Диапазон_суммирования не заполняем. (Рис. 10).
Получаем искомую сумму, в нашем случае это число 400.
Сумма, если не равно.
Формулы в ячейках, как пример, выглядят вот так:
Рассмотрим на примере, когда нам нужно суммировать все значений, кроме тех, которые находятся напротив ячейки с Пример 1.
Выбираем в Диапазон ячейки, содержащие наш критерий (D3:D27) – Пример 1
В Критерий прописываем: <> Пример 1. Получаем сумму всех значений (диапазон E3:E27), кроме тех, которые стоят напротив Пример 1 (диапазон (D3:D27)).
В Диапазон_суммирования выбираем ячейки, значения которых нужно суммировать (E3:E27). Нажимаем ОК. Получаем нужную нам сумму. (Рис. 11).
Или нам нужно суммировать только значение не равные числу 100.
Тогда в строке Диапазон указываем нужный нам диапазон для суммирования (E3:E27).
В Критерий пишем <>100.
Диапазон_суммирования не заполняем.
Нажимаем ОК. Получаем сумму всех ячеек, кроме тех, где значение равно числу 100. (Рис. 12).
Сумма если больше или равно, Сумма если меньше или равно. СУММЕСЛИ.
Знаки, которые используются: >=, =500″).
Рассмотрим на примере. Нам нужно посчитать сумму значений в столбце (E3:E27). Но суммировать нужно только те числа, которые равны или больше значения числа 500. По стандартному алгоритму вызываем функцию СУММЕСЛИ.
В строке Диапазон выбираем диапазон ячеек, которые нужно просуммировать (E3:E27).
В Критерий пишем:>=500.
Диапазон_суммирования не заполняем.
Нажимаем ОК. Получаем сумму значений равною числу 500, плюс все, что больше этой цифры в заданном диапазоне (E3:E27) (Рис. 13).
Сумма если меньше или равно
Формула в ячейке, как пример, выглядит вот так:
Пример функции СУММЕСЛИ для суммирования в Excel по условию
Функция СУММЕСЛИ в Excel используется для расчета суммы числовых значений, содержащихся в диапазоне ячеек, с учетом критерия, указанного в качестве одного из аргументов, и возвращает соответствующее числовое значение. Данная функция является альтернативой совместного использования функций СУММ и ЕСЛИ. Ее использование позволяет упростить формулы, поскольку критерий, по которому производится суммирование значений, предусмотрен непосредственно в ее синтаксисе.
Примеры использования функции СУММЕСЛИ в Excel
Пример 1. В таблице Excel записаны члены геометрической прогрессии. Какую часть (в процентах) составляет сумма первых 15 членов прогрессии от общей суммы ее членов.
Вид исходной таблицы данных:
Выполним расчет с помощью следующей формулы:
Исходная таблица имеет следующий вид:
Для расчета используем функцию в формуле:
Функция ЕСЛИ выполняет проверку возвращаемых значений функциями СУММЕСЛИ с условиями проверки «Иванов» и «Петров» соответственно и возвращает текстовую строку с фамилией продавца, суммарная прибыль которого оказалась больше.
В итоге получим следующее значение:
Как в Excel суммировать ячейки только с определенным значением
Пример 3. В таблице указаны данные о зарплате сотрудника на протяжении 12 месяцев прошлого года. Рассчитать доходы работника за весенние месяцы.
Вид таблицы данных:
Весенними месяцами являются месяца с номерами 3, 4 и 5. Для расчета используем формулу:
Сумма зарплат с 6-го по 12-й месяц является подмножеством множества суммы зарплат с 3-го по 12-й месяц. Разница этих сумм является искомой величиной – суммой зарплат за весенние месяцы:
Функцию СУММЕСЛИ можно использовать если требуется определить сразу несколько значений для различных критериев. Например, для расчета суммы зарплат за три первых и три последних месяца в году соответственно составим следующую таблицу:
Для расчетов используем следующую формулу:
В результате получим:
Особенности использования функции СУММЕСЛИ в Excel
Функция СУММЕСЛИ имеет следующий синтаксис:
=СУММЕСЛИ( диапазон; условие; [диапазон_суммирования])
В этом руководстве объясняется различие между функциями СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) с точки зрения их синтаксиса и использования, а также приводятся примеры формул для суммирования значений с несколькими критериями в Excel 2016, 2013, 2010, 2007, 2003 и ниже.
Как известно, Microsoft Excel предоставляет множество функций для выполнения различных расчетов с данными. Мы уже рассмотрели СУММЕСЛИ, которая суммирует числа, соответствующие указанным критериям. Теперь пришло время перейти к расширенной версии этой функции СУММЕСЛИМН, которая позволяет найти сумму по нескольким условиям.
Как работает СУММЕСЛИМН?
При помощи СУММЕСЛИМН можно найти сумму величин, для которых есть много условий. Она появилась впервые в MS Excel 2007, поэтому вы можете использовать ее во всех современных версиях программы.
По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:
СУММЕСЛИМН(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2],…)
Первые 3 аргумента являются обязательными, а вот дополнительные диапазоны и связанные с ними условия являются необязательными.
условие1— обязательное первое условие, которое должно быть выполнено. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, «> = 10», A1, «яблоко» или СЕГОДНЯ().
Важно! Функция СУММЕСЛИМН работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным критериям (все требования соблюдаются для этой ячейки).
1. Порядок аргументов
Аргументы применяются по-разному. В частности, диапазон_сумирования является 1-м параметром в СУММЕСЛИ, но является третьим в СУММЕСЛИМН.
На первый взгляд может показаться, что Microsoft намеренно усложняет процесс обучения для своих пользователей. Однако при ближайшем рассмотрении вы увидите причины этого. Дело в том, что этот диапазон является необязательным в СУММЕСЛИ. Если вы его опустите, то нет никаких проблем, ваша формула будет складывать в диапазоне поиска (первый параметр).
В СУММЕСЛИМН он, напротив, очень важен и обязателен, и поэтому и стоит первым. Вероятно, ребята из Microsoft подумали, что после добавления 10- й или 100- й пары диапазон/критерий кто-то может забыть указать диапазон для суммирования:)
2. Диапазон суммирования и область критериев должны быть одинакового размера
В функции СУММЕСЛИ эти аргументы не обязательно должны иметь одинаковую размерность. Достаточно указать начальную точку. В СУММЕСЛИМН они должны содержать одинаковое количество строк и столбцов.
Выражение =СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F22;I3) вернет сообщение об ошибке #ЗНАЧ!, так как второй параметр поиска (F2:F22) не совпадает по размеру с остальными (E2:E21) и (C2:C21).
Хорошо, хватит стратегии (т.е. теории), давайте перейдем к тактике (к примерам).
Суммирование с множеством условий.
Имеются данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».
Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.
В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.
В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.
Если ОБА эти требования выполняются, то такой заказ нам подходит, и его стоимость мы учтём.
Как видите, у нас найдено 2 совпадения, в которых был продан молочный шоколад.
Использование операторов сравнения.
Рассчитаем по покупателю «Красный» стоимость заказов, в которых было более 100 единиц товара. Как видим, здесь нужно использовать и текстовый, и числовой критерий.
Критерии можно записать в саму формулу, и выглядеть это будет так:
Но более рационально использовать ссылки, как это и сделано на рисунке:
Примечание. Обратите внимание, что в формулах логические выражения с операторами сравнения всегда должны быть заключены в двойные кавычки («»).
Синтаксис, а также работа с числами, текстом и датами у этой функции точно такие же, как и СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу о условном суммировании.
А как еще можно решить нашу задачу?
Способ 2. Используем функцию СУММПРОИЗВ.
Разберем подробнее, как работает СУММПРОИЗВ():
Результатом вычисления B2:B21=$I$12 является массив
ИСТИНА означает соответствие кода покупателя условию, т.е. слову Красный. Массив этот можно увидеть, выделив в строке формул B2:B21=$I$12, а затем нажав F9.
И в результате логический массив превратится в массив чисел <0:1:0:0:0:0:0:0:0:0:0:0:1:0:0:0:1:0:0:0>.
Результатом вычисления D2:D21>I13 является массив
ИСТИНА соответствует ограничению «количество больше 100». Здесь мы также применяем двойное отрицание, чтобы преобразовать логические переменные в числа.
Результатом поэлементного умножения этих трех массивов является <0:23210:0:0:0:0:0:0:0:0:0:0:0:0:0:0:15840:0:0:0>. Суммируем эти произведения и получаем 39050.
Способ 3. Формула массива.
И еще один вариант расчета – применим формулу массива. В I14 запишем:
Не забудьте в конце нажать комбинацию клавиш CTRL+SHIFT+ENTER, чтобы обозначить это выражение как формулу массива. Фигурные скобки в начале и в конце программа добавит автоматически. Вновь получим результат 39050.
Способ 4. Автофильтр.
Еще один альтернативный вариант – применение автофильтра. Для этого преобразуйте диапазон данных A1:F21 в «умную» таблицу. Напомню, что для этого в меню «Главная» выберите «Форматировать как таблицу». После этого добавьте в нее строку итогов (вкладка «Конструктор») и установите необходимые фильтры.
Без всяких формул итог по отфильтрованным строкам будет определён.
Как СУММЕСЛИМН работает с датами?
Если вы хотите отобрать и сложить какие-то показатели в определенном временном интервале на основе текущей даты, используйте функцию СЕГОДНЯ() в ваших ограничениях, как это показано ниже.
Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце А попадает в последние 7 дней, включая сегодняшний день (предполагается, что сегодня 7 февраля):
При анализе отчетов и других данных вам часто может потребоваться суммировать данные, соответствующие пустым или непустым клеткам таблицы.
А теперь давайте посмотрим, как вы можете использовать формулу СУММЕСЛИМН с «пустыми» и «непустыми» ячейками для реальных данных.
По покупателю «Красный» рассчитаем количество товара в невыполненных заказах. Для этого в столбце B ищем соответствующее название клиента, а в F – пустую ячейку. Если оба требования совпадают, складываем количество товара из столбца D.
Каждое из этих выражений дает верный результат – 144 единицы в заказе от 4 февраля.
Сумма нескольких условий.
А теперь давайте рассчитаем общую стоимость выполненных заказов по двум товарам.
Если мы просто добавим второй критерий в I3, и вместо I2 используем область I2:I3, то расчет будет неверным, поскольку в C2:C21 будем искать товар, в названии которого есть И «черный», И «молочный» одновременно. Ведь таких просто нет.
Поэтому первый вариант расчета таков:
Просто складываем выполненные заказы сначала по первому, а затем по второму наименованию.
Второй вариант: используем элемент массива критериев и функцию СУММ.
Как видите, результаты получены одинаковые. Выбирайте способ, который будет для вас проще и понятнее.