Функция СУММЕСЛИ в Excel с примерами

В предыдущей статье мы рассмотрели синтаксис функции СУММЕСЛИ в Excel, теперь давайте закрепим знания на практике при помощи ряда примеров формулы СУММЕСЛИ:

СУММЕСЛИ в Excel примеры с логическими операторами (больше, меньше или равно)

Давайте рассмотрим несколько примеров формул СУММЕСЛИ, которые вы можете использовать для суммирования значений для условий больше чем, меньше чем или равно заданному значению.

Примечание . Обратите внимание, что в формулах Excel СУММЕСЛИ оператор сравнения, за которым следует число или текст, всегда должен быть заключен в двойные кавычки («»).

Пример формулы СУММЕСЛИ

Сумма, если больше

Суммирует значения больше 5 в ячейках A2:A10.

Сумма, если меньше

=СУММЕСЛИ(A2:A10; «<10»; B2:B10)

Суммирует значения в ячейках B2:B10, если соответствующее значение в столбце A меньше 10.

Сумма, если равно

(можно не указывать)

Суммирует значения в ячейках B2:B10, если соответствующая ячейка в столбце A не равна значению в ячейке D1.

Сумма если больше или равно

Суммирует значения, которые больше или равны 5 в диапазоне A2:A10.

Сумма если меньше или равно

=СУММЕСЛИ(A2:A10; «<=10»; B2:B10)

Суммирует значения в ячейках B2:B10, если соответствующее значение в столбце A меньше либо равно 10.

СУММЕСЛИ в Excel примеры с текстовым критерием

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

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

Пример формулы СУММЕСЛИ

Сумма, если равно

=СУММЕСЛИ(A2:A8; «бананы»; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит точное слово «бананы» и никакие другие слова или символы. Ячейки, содержащие «зеленые бананы», «бананы зеленые» или «бананы!» не будут считаться.

=СУММЕСЛИ(A2:A8; «*бананы*»; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит слово «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «зеленые бананы» или «бананы зеленые», будут учитываться для суммирования.

Сумма, если не равно

=СУММЕСЛИ(A2:A8; «<>бананы»; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит любое значение, отличное от слова «бананы». Если ячейка содержит «бананы» вместе с некоторыми другими словами или символами, такими как «желтые бананы» или «бананы желтые», такие ячейки будут учитываться для суммирования.

=СУММЕСЛИ(A2:A8; «<>*бананы*»; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A не содержит слова «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «желтые бананы» или «бананы желтые», не суммируются.

Для получения дополнительной информации о частичном совпадении см. пункт СУММЕСЛИ примеры формул с подстановочными знаками.

А теперь, давайте посмотрим пример формулы «Сумма, если не равно» в действии. Как показано на изображении ниже, формула суммирует количество всех продуктов, кроме «Банана Дамский пальчик»:

=СУММЕСЛИ(A2:A8; «<>Банан Дамский пальчик»; C2:C8)

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с проверкой на неравенство

Примечание . Как и большинство других функций Excel, СУММЕСЛИ нечувствительна к регистру, что означает, что «<> бананы», «<> Бананы» и «<> БАНАНЫ» будут давать точно такой же результат.

СУММЕСЛИ в Excel примеры операторов сравнения со ссылками на ячейки

Если вы хотите получить более универсальную формулу Excel СУММЕСЛИ, вы можете заменить числовое или текстовое значение в критериях ссылкой на ячейку, например:

В этом случае вам не придется менять формулу СУММЕСЛИ, основанную на другом критерии – вы просто вводите новое значение в ссылочной ячейке.

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ, суммирование исключая значение в ячейке F1

Примечание. Когда вы используете логическое выражение с ссылкой на ячейку, вы должны использовать двойные кавычки («»), чтобы начать текстовую строку и амперсанд (&), чтобы объединить и завершить строку, например «<>» и F1.

Оператор «равенства» (=) можно не использовать до ссылки на ячейку, поэтому обе приведенные ниже формулы эквивалентны и правильны:

Читайте так же:

Как в microsoft word сделать альбомный лист

Если вы намерены условно суммировать ячейки на основе «текстовых» критериев и хотите суммировать путем частичного совпадения, вам нужно использовать подстановочные знаки в формуле СУММЕСЛИ.

Доступны следующие подстановочные знаки:

Звездочка (*) — представляет любое количество символов

Знак вопроса (?) — представляет один символ в определенном месте

Пример 1. Суммирование значений, основанные на частичном совпадении

Предположим, вы хотите суммировать количество, относящиеся ко всем видам бананов. Следующие формулы СУММЕСЛИ будут очень эффективны в таких случаях:

=СУММЕСЛИ(A2:A8; «*бананы*»;C2:C8) — критерий включает текст, заключенный в звездочки (*).

=СУММЕСЛИ(A2:A8; «*»&F1&»*»; C2:C8) — критерий включает ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для конкатенации строки.

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с подстановочными знаками для суммирования по частичному совпадению

Если вы хотите считать только те ячейки, которые начинаются или заканчиваются определенным текстом, добавьте только один * до или после текста:

=СУММЕСЛИ(A2:A8; «бананы*»; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A начинается со слова «бананы».

=СУММЕСЛИ(A2:A8; «*бананы»; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A заканчивается словом «бананы».

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

Пример 2. Суммирование по заданному количеству символов

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

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с условием суммирования, если длина текстовой строки в шесть букв

Пример 3. Сумма ячеек, соответствующих текстовым значениям

Если ваш рабочий лист содержит разные типы данных, и вы хотите только суммировать ячейки, соответствующие текстовым значениям, пригодится следующая формула СУММЕСЛИ:

=СУММЕСЛИ(A2:A8; «?*»; C2:C8) – суммирует значения из ячеек C2:C8, если соответствующая ячейка в столбце A содержит не менее 1 символа.

=СУММЕСЛИ(A2:A8; «*»; C2:C8) – учитывает пустые ячейки, содержащие строки нулевой длины, возвращаемые некоторыми другими формулами, например =»».

Обе приведенные выше формулы игнорируют нетекстовые значения, такие как ошибки, логические значения, числа и даты.

Пример 4. Использование * или ? как обычные символы

Если вы хотите использовать либо *, либо ? для обработки в функции СУММЕСЛИ как литерала, а не подстановочного знака, то используйте перед этим знаком тильду (

). Например, следующая формула СУММЕСЛИ просуммирует значения в ячейках C2:C8, если ячейка в столбце A в той же строке содержит знак вопроса:

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

СУММЕСЛИ в Excel примеры с датами

Как правило, функцию СУММЕСЛИ используют для условного суммирования значений на основе дат так же, как и с текстовыми и числовыми критериями.

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

Пример формулы СУММЕСЛИ

Сумма по определенной дате

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B равна 29.10.2017.

Сумма, если дата больше либо равна заданной в формуле дате

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B больше или равна 29.10.2017.

Сумма, если дата больше даты, указанной в ячейке

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B больше даты, указанной в ячейке F1.

Если вы хотите суммировать значения на основе текущей даты, вам необходимо использовать СУММЕСЛИ в сочетании с функцией СЕГОДНЯ(), как показано ниже:

Пример формулы СУММЕСЛИ

Суммирование значений, за текущую дату

=СУММЕСЛИ(B2:B9; СЕГОДНЯ (); C2:C9)

Суммирование значений, меньше текущей даты, то есть до сегодняшнего дня.

=СУММЕСЛИ(B2:B9; «<«&СЕГОДНЯ(); C2:C9)

Суммирование значений, больше текущей даты, то есть будущие даты относительно сегодняшнего дня.

Читайте так же:

Можно ли включать одновременно духовку и комфорку

=СУММЕСЛИ(B2:B9; «>»& СЕГОДНЯ(); C2:C9)

Суммирование значений за неделю от текущей даты. (т.е. сегодня + 7 дней).

=СУММЕСЛИ(B2:B9; » Пример функции СУММЕСЛИ с суммированием количества продуктов, которые будут отправлены через неделю — Функция СУММЕСЛИ в Excel» src=»https://naprimerax.org/images/751/primer-funktcii-summesli-s-summirovaniem-kolichestva-produktov-kotorye-budut-otpravleny-cherez-nedeliu-funktciia-summesli-v-excel.png» alt=»9-funktciia-summesli-v-excel» />

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с суммированием количества продуктов, которые будут отправлены через неделю

СУММЕСЛИ в заданном диапазоне дат

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

=СУММЕСЛИ(B2:B9; «>=01.11.2017»; C2:C9) — СУММЕСЛИ(B2:B9; «>=01.12.2017»; C2:C9)

Эта формула суммирует значения в ячейках C2:C9, если дата в столбце B находится между 1 ноября 2017 года и 30 ноября 2017, включительно.

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ дата в диапазоне

Эта формула может показаться немного сложной с первого взгляда, но при более близком рассмотрении это выглядит довольно просто. Первая функция СУММЕСЛИ объединяет все ячейки в C2:C9, где соответствующая ячейка в столбце B больше или равна дате начала (в данном примере 1 ноября). Затем вам просто нужно вычесть значения, которые попадают после даты окончания (30 ноября), с помощью второй функции СУММЕСЛИ.

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

Как суммировать количество ячеек с использованием диапазона переменных

Вы можете встроить функцию INDIRECT в качестве аргумента внутри функции SUM, чтобы создать диапазон переменных ссылок на ячейки для добавляемой функции SUM. Функция INDIRECT делает это, косвенно ссылаясь на диапазон ячеек через промежуточную ссылку на ячейку.

Формат обеих функций, используемых вместе, выглядит следующим образом:

Это блокирует диапазон, начинающийся с D1, и позволяет D4 изменять, если вы вставляете или удаляете любые ячейки в столбце D.

Вы также можете использовать другие ячейки в электронной таблице для изменения ссылок на ячейки. Например, если вы используете E1 для ссылки на первую ячейку диапазона и E2 для ссылки на последнюю ячейку диапазона, формула выглядит следующим образом:

Изменяя числа, расположенные в ячейках E1 и E2, вы можете изменить диапазон в формуле без необходимости вручную редактировать формулу.

СУММЕСЛИМН

Аргументы

Теперь рассмотрим аргументы для функции СУММЕСЛИМН в Excel.

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

Диапазон суммирования – это столбец, значения из которого будут выбираться, если они соответствуют одному и второму заданному условию. В примере, это С2:С26 .

Диапазон условия1 – это первый диапазон для сравнения. Нужно выбрать пол ученика, поэтому напишем D2:D26 .

Условие1 – нужно указать то значение, которое будет выбираться из Диапазона условий1. Нас интересуют мальчики, поэтому укажем D3 .

Диапазон условия2 – второй диапазон сравнения. Здесь будем выбирать класс, в котором учится ребенок. Соответственно укажем В2:В26 .

Условие2 – конкретное значение, которое нас интересует из Диапазона условий2. Сначала нужно будет выделить ячейку, в которой указан «8 класс» , затем «9 класс» .

Пример

Решать будем вторую задачу. Выделяем ячейку Н8 , ставим «=» , пишем СУММЕСЛИМН и открываем скобку «(» . В качестве Диапазона суммирования указываем столбец со «Средним балом» : С2:С26 . Диапазон условия1 – это пол ученика: D2:D26 , Условие1 – это мальчики: ячейка D3 . Диапазон условия2 – это класс: В2:В26 , Условие2 – 8 класс: ячейка В3 . Закройте скобку, нажмите «Enter» .

В «Строке формул» сразу сделайте абсолютные ссылки на столбцы: Класс, Средний бал, Мал/Дев.

Растягиваем формулу на Н8:Н11 . Теперь укажем правильные ячейки в качестве Условия1 и Условия2.

В Н9 считается средний балл для мальчиков, которые учатся в 9 классе. Поэтому, Условие1 для первого диапазона «мальчик» : D3 , Условие2 для второго диапазона «9» : В6 .

Читайте так же:

Как в биосе выставить загрузку с usb

В Н10 считаем средний балл девочек, которые учатся в 8 классе. Условие1 – «девочка» , D4 , Условие2 – «8» , В3 .

В Н11 считаем средний балл девочек, которые учатся в 9 классе. Условие1 – «девочка» , D4 , Условие2 – «9» , В2 .

Таким образом, мы посчитали сумму среднего балла для мальчиков и девочек, которые учатся в 8 и 9 классах по отдельности.

Давайте теперь проверим, чтобы результат был правильным. Посчитаем сумму «Среднего бала» для всех значений в исходной таблице, для Задачи 1 и для Задачи 2. Сумма должна быть одинаковой.

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

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

В формуле, указанной выше, мы используем функцию СЕГОДНЯ для автоматического вычисления текущей даты.

Здесь, в первом критерии для вычисления нужной даты мы указываем функции условие, при котором нужно суммировать данные за период равный или меньше текущей даты.

Во втором критерии мы указываем функции, что нужно суммировать данные больше или равные текущей дате минус 6 дней.

Таким образом, диапазон двух дат в формуле теперь выглядит как: “Посчитай данные за период с сегодняшнего дня, до минус 6 дней от сегодняшнего дня”.

Если у вас остались вопросы по этому примеру оставляйте их в комментариях.

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

Формулы Excel "СУММЕСЛИ" и "СЧЕТЕСЛИ" при расчете сезонности

Если мы хотим быстро, не задумываясь, без ошибок рассчитать сезонность для длинных временных рядов, то рекомендую использовать 2 формулы Excel — «СУММЕСЛИ» и «СЧЕТЕСЛИ».

Часто временной ряд оказывается длинным — продажи по месяцам за 4 — 5 лет и больше, и еще задача может усложнится тем, что первый и последний год могут оказаться не полными, т.е. данные начинаются не с января, а заканчиваются не декабрем, а мы хотим быстро и правильно усреднить сезонные отклонения и получить сезонность. Для этого воспользуемся 2 формулами Excel — суммесли и счетесли.

Рассмотрим использование формул «СУММЕСЛИ» и «СЧЕТЕСЛИ»
на примере расчета прогноза с помощью модели «Скользящей средней к 3-м месяцам с аддитивной сезонностью».

Рассчитаем:

Скользящую среднюю к 3-м месяцам;
Разность между значениями ряда и средними значениями к 3-м месяцам (пункт 1);
Усредним разность ряда и средней для каждого месяца получим сезонность в абсолютной величание — для этого воспользуемся функциями СУММЕСЛИ и СЧЕТЕСЛИ;
Продлим значения ряда с помощью скользящей средней к 3-м месяцам и скорректируем её аддитивной сезонностью.

1. Скользящую среднюю к 3-м месяцам для каждого момента времени во временном ряду начиная с 4-го периода:

2. Разность между значениями ряда и средними значениями к 3-м месяцам для каждого момента времени t (пункт 1):

3. Усредним разность ряда и средней (пункт 2) для 12 месяцев — получим сезонность в абсолютной величание — аддитивную сезонность.

Для этого вначале выделим номера месяцев с помощью функции Excel =месяц(дата).

Чтобы воспользоваться функцией Excel =месяц(дата), проверяем, являются ли наши даты «январь 2010 г.» датами, если нет, функция =месяц() выдаст ошибку.

Получаем ряд с пронумерованными месяцами от 1 до 12 для каждого момента времени t:

Далее с помощью функции Excel =СУММЕСЛИ суммируем для каждого месяца от 1 до 12 отклонения, которые мы считали в пункте 2.

Для этого в формулу СУММЕСЛИ передаем следующие значения:

диапазон — $G$8:$AY$8 — диапазон с номера месяцев за весь период;
критерий — D9 — номер месяца для которого суммируем отклонения и рассчитываем сезоность;
диапазон для суммирования — $G$7:$AY$7 — диапазон с отклонениями рассчитанными в пункте 2).

Фиксируем ссылки на диапазон с номерами месяцев и диапазон с отклонениями (как зафиксировать ссылки описано в статье «Как зафиксировать ссылку в Excel?»), и протягиваем формулу для 12 месяцев от 1 до 12.

Читайте так же:

Как в ворде отображать одну страницу

Получаем сумму отклонений для каждого месяца.

Далее с помощью формулы Excel =СЧЁТЕСЛИ() считаем количество месяцев во временном ряду, на которое мы разделим сумму отклонений и получим среднее абсолютное отклонение для каждого месяца — аддитивную сезонность.

И так передаем в формулу =СЧЁТЕСЛИ(

диапазон — $G$8:$AY$8 — фиксируем ссылку — номера месяцев во временном ряду;
критерий — D9 — номера месяцев для сезонности, кол-во которых нам надо рассчитать во временном ряду для того чтобы рассчитать среднее отклонение.

Протягиваем формулу Excel =СЧЁТЕСЛИ от 1 до 12 и получаем ряд с количеством месяцев для каждого месяца сезонности:

Далее сумму отклонений делим на количество месяцев в расчете сезонности, получаем абсолютную (аддитивная) сезонность для каждого месяца в году:

Данный расчет мы проделали в 3 шага

Просуммировали отклонения для каждого месяца сезонности с помощью СУММЕСЛИ,
Рассчитали количество периодов для каждого месяца сезонности с помощью СЧЁТЕСЛИ,
И разделили пункт 1 на пункт 2, т.е. сумму отклонений на количество периодов, т.е. нашли среднее отклонение для каждого месяца.

Но эти 3 шага можно совместить в один и сразу получить 12 значений аддитивной сезонности.
Для этого СУММЕСЛИ разделим на СЧЁТЕСЛИ, как на картинке:

Формула получается следующего вида:

Вводим формулу, протягиваем на 12 месяцев, получаем сезонность:

Еще раз её разберем =СУММЕСЛИ($G$8:$AY$8;D9;$G$7:$AY$7)/СЧЁТЕСЛИ($G$8:$AY$8;D9)

СУММЕСЛИ($G$8:$AY$8 (зафиксированный диапазон с номерами месяцев);D9 (номер месяца, для которого мы рассчитываем сезонность); $G$7:$AY$7 (зафиксированный диапазон со значениями разности между рядом и средней — пункт 2))

СЧЁТЕСЛИ($G$8:$AY$8 (зафиксированный диапазон с номерами месяцев);D9 (номер месяца, для которого мы рассчитываем сезонность))

Теперь давайте рассчитаем прогноз.

Для этого продлим значения ряда с помощью скользящей средней к 3-м месяцам и скорректируем её аддитивной сезонностью.

Продлим скользящую среднею к 3-м месяцам в будущее на 12 месяцев:

Скорректируем скользящую сезонностью.

Для этого к скользящей средней прибавим аддитивную сезонность. Сезонность для каждого месяца подтянем с помощью функции Excel ГПР.

Подробнее об этом читайте в статье «ГПР в Excel»

Прогноз = средние продажи за последние 3 месяца + сезонность:

=СРЗНАЧ(AW4:AY4 (средние продажи за 3 последних месяца))+ГПР(AZ3 (искомый номер месяца$D$9:$O$13 (зафиксированная ссылка на таблицу с сезонностью);5 (номер строки с сезонностью);0)

Очень часто сталкиваюсь с ошибками посетителей наших онлайн курсов по прогнозированию, когда усредняют отклонения воспользовавшись стандартной формулой СРЗНАЧ и одну из ссылок ставят на не соответствующий месяц, т.е. считаем средние отклонения января, а одна из ссылок оказывается на декабрь или февраль, в результате чего коэффициент сезонности получается не верным.

Для того чтобы этой ошибки сознательно избежать и заставить думать машину используйте формулы Excel =СУММЕСЛИ и СЧЁТЕСЛИ. Комбинация данных формул поможет вам рассчитать среднее значение по заданному критерию.

А если хотите на 100% избежать ошибок в расчетах прогнозов, да еще и автоматически подобрать нужную модель для каждого временного ряда работайте с Forecast4AC PRO.

Forecast4AC PRO сэкономит вам кучу времени позволит делать более точные прогнозы.

Если есть вопросы или комментарии, пожалуйста, пишите в форме ниже!

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast Lite — автоматический расчет прогноза в Excel .
4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Даны 3 столбца, в которых указана дата, тип расходов, сумма на конкретный вид траты. Необходимо сделать выборку по двум условиям:

В качестве самостоятельного задания пользователь может ввести условие по дате за конкретный период: за месяц или неделю. Чтобы это сделать, необходимо в кавычках указать период, к примеру, «>01.01.2017». Вторым условием выступает: « Понравилась статья? Поделись с друзьями:

Читайте так же:

Можно ли в майле восстановить удаленные письма

Как использовать СУММЕСЛИ – Примеры.

С синтаксисом разобрались теперь давайте попробуем на практике использовать функцию СУММЕСЛИ.

СУММЕСЛИ больше, меньше или равно

Давайте взглянем на некоторые формулы СУММЕСЛИ, которые можно использовать для суммирования значений, которые больше, меньше или равны заданному значению.

Поясним примеры из скриншота выше:

Пример1. Складывает значения больше 6 в диапазоне ячеек A5: A15.
Пример2. Складывает значения в диапазоне ячеек B5: B15, если значение соответствующего столбца A меньше 8.
Пример3. Складывает значения в ячейках A5: A15, которые равны значению в ячейке С4. Два варианта написания
Пример4. Складывает значения в ячейках B5: B15, если соответствующая ячейка в столбце A отличается от значения в ячейке С4.
Пример5. Складывает значения больше или равные 8 в диапазоне A5: A15.
Пример6. Складывает значения в ячейках B5: B15, если соответствующее значение в столбце A меньше или равно 8.

СУММЕСЛИ с текстовыми критериями

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

Поясним примеры из скриншота выше:

Пример1. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит в точности слово «вишня» и никаких других слов или символов. Ячейки, содержащие «черешня» и «яблоки», не будут включены.
Пример2. Складывает значения ячеек В2:В6, если соответствующая ячейка в столбце A содержит слово «шня» отдельно или в сочетании с любым другим словом или символом. Ячейки, содержащие «черешня» и «вишня», будут добавлены т.к содержат в себе «шня».
Пример3. Складывает значения ячеек В2: В6, если соответствующая ячейка в столбце A содержит любое значение, кроме «Вишня».
Пример4. Складывает значения ячеек В2: В6, если соответствующая ячейка в столбце A не содержит слова «шня» ни отдельно, ни в сочетании с любым другим словом. В данном случае будет выведена только сумма яблок.

Как и большинство других функций Excel, СУММЕСЛИ не учитывает регистр, что означает, что «Вишня», «ВИШНЯ» и «вишня» дадут точно такой же результат.

Используйте операторы сравнения со ссылками на ячейки

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

= СУММЕСЛИ (A2: A8; «<>» & F1; C2: C8)

В этом случае вам не придется изменять формулу для условного суммирования на основе других критериев — вам просто нужно ввести новое значение в ссылочную ячейку.

Формулы СУММЕСЛИ с подстановочными знаками

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

Звездочка (*): представляет любое количество символов
Знак вопроса (?): Обозначает один символ в определенной позиции.
Амперсанд(&): Обозначает сложение текста. В примере ниже есть описание как это работает.

Поясним примеры из скриншота выше:

Пример1. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение т.е в начале и конце любое количество символов.
Пример2. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение т.е последняя буква может быть любая. В примере я намерено допустил ошибку и написал вишна.
Пример3. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение. критерии включают ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) перед и после ссылки на ячейку для объединения строки.

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

Формулы СУММЕСЛИ с условием даты

На приведенном ниже скриншоте показано, как можно использовать формулу для определения общего количества выручки начиная со 2 декабря.

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

голоса

Рейтинг статьи