Excel если сумм действие

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

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

СУММЕСЛИ и ее синтаксис

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

1. Диапазон – ячейки, которые следует оценить на основании критерия (заданного условия).
2. Критерий – определяет, какие ячейки из диапазона будут выбраны (записывается в кавычках).
3. Диапазон суммирования – фактические ячейки, которые необходимо просуммировать, если они удовлетворяют критерию.

Получается, что у функции всего 3 аргумента. Но иногда последний может быть исключен, и тогда команда будет работать только по диапазону и критерию.



Как работает функция СУММЕСЛИ в Excel?

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

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

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

Прописываем аргументы.

1. Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Поэтому проставляем E2:E14.
2. Критерий выбора в нашем случае – продавец. Заключаем слово в кавычки и ставим вторым аргументом.
3. Диапазон суммирования – это заработные платы, потому что нам нужно узнать сумму зарплат всех продавцов. Поэтому F2:F14.

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

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

Функция СУММЕСЛИ в Excel с несколькими условиями

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

Синтаксис с использованием функции по нескольким критериям

Аргументов у СУММЕСЛИМН может быть сколько угодно, но минимум – это 5.

1. Диапазон суммирования. Если в СУММЕСЛИ он был в конце, то здесь он стоит на первом месте. Он также означает ячейки, которые необходимо просуммировать.
2. Диапазон условия 1 – ячейки, которые нужно оценить на основании первого критерия.
3. Условие 1 – определяет ячейки, которые функция выделит из первого диапазона условия.
4. Диапазон условия 2 – ячейки, которые следует оценить на основании второго критерия.
5. Условие 2 – определяет ячейки, которые функция выделит из второго диапазона условия.

И так далее. В зависимости от количества критериев, число аргументов может увеличиваться в арифметической прогрессии с шагом 2. Т.е. 5, 7, 9…

Пример использования

Предположим, нам нужно подсчитать сумму заработных плат за январь всех продавцов-женщин. У нас есть два условия. Сотрудник должен быть:

• продавцом;
• женщиной.

Значит, будем применять команду СУММЕСЛИМН.

Прописываем аргументы.

• диапазон суммирования – ячейки с зарплатой;
• диапазон условия 1 – ячейки с указанием должности сотрудника;
• условия 1 – продавец;
• диапазон условия 2 – ячейки с указанием пола сотрудника;
• условие 2 – женский (ж).

Итог: все продавцы-женщины в январе получили в сумме 51100 рублей.

СУММЕСЛИ в Excel с динамическим условием

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

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

Копируем данные сотрудника и вставляем их в общий перечень. Суммы в итоговых ячейках изменились. Функции среагировали на появление в диапазоне еще одного продавца-женщины.

Аналогично можно не только добавлять, но и удалять какие-либо строки (например, при увольнении сотрудника), изменять значения (заменить «январь» на «февраль» и подставить новые заработные платы) и т.п.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формула 1: =СУММЕСЛИ(A2:A8; «=» & F1; C2:C8)

Формула 2: =СУММЕСЛИ(A2:A8; F1; C2:C8)

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

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

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

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

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

Пример 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. Суммирование по заданному количеству символов

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

=СУММЕСЛИ(A2:A8; «??????»; C2:C8)

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

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

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

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

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

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

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

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

=СУММЕСЛИ(A2: A8; «~?»; C2: C8)

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

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

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

Если вы хотите суммировать значения, соответствующие датам, которые больше или меньше указанной вами даты, используйте операторы сравнения, которые мы рассматривали выше. Ниже приведены примеры формул 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 ноября), с помощью второй функции СУММЕСЛИ.

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

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

Я покажу, как устроен синтаксис этих функций в Microsoft Excel и для каких задач их можно применить.

Разбор синтаксиса СУММЕСЛИ

Начать стоит с правил ввода составляющих функции СУММЕСЛИ. Из ее частей формируется СУММЕСЛИМН, поэтому детальное описание второй опустим, а вернемся к ней только в завершающем разделе статьи. Стандартная запись функции выглядит как =СУММЕСЛИ(A1:A10;»>1″). 

Значит, что суммироваться будут только те ячейки, которые подпадают под указанный критерий, то есть больше 1. Это удобно, например, когда вам нужно посчитать, сколько у вас всего значений, меньше, больше или равняются конкретному. Если вызвать окно «Аргументы функции», вы увидите более понятный вариант записи и можете использовать его, указав диапазон и критерий.

Задать значение для «Диапазон_суммирования» нужно только в том случае, если вы ищете значения больше или меньше конкретного, но при этом добавлять в сумму необходимо ячейки из другого столбца (пока это звучит непонятно, но далее вы увидите такое представление функции на наглядном примере).

Запись СУММЕСЛИ при неравенстве

Еще раз пройдемся по базовому типу использования СУММЕСЛИ. После объявления функции укажите диапазон для проверки в виде A1:A20;. Обязательно поставьте точку с запятой, ведь это нужно для корректной работы функции.

После этого введите вручную сам критерий (нужно именно указывать число, а не брать его из конкретной ячейки с записью A1). Выберите знак больше, меньше, равно или <>, чтобы указать, что число не равняется конкретному. 

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

Использование СУММЕСЛИ с текстом

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

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

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

2. Затем вместо неравенства введите текст для поиска.

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

4. Это то самое значение «Диапазон_суммирования», о котором я говорил при просмотре окна «Аргументы функции».

5. На следующем скриншоте вы видите, что морковок в списке всего две, каждая запись имеет значение 12, а это значит, что в сумму попадают только эти два числа, в результате получается 24.

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

Использование функции СУММЕСЛИМН 

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

Посчитаем количество груш, но только тех, чья цена будет выше 10 за единицу. Соответственно, это только пример, а вы можете использовать функцию для совершенно разных задач как при работе с текстовыми значениями, так и неравенствами.

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

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

3. Второй критерий – цена, которая должна превышать 10 за единицу. Соответственно, впишите блок с неравенством A1:A10;»>10″, где A1:A10 – диапазон ячеек, а >10 – критерий.

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

При первой записи у вас могут возникнуть трудности с правильным написанием функции, ведь она содержит много условий. Я оставлю вам ее отдельно, чтобы вы могли скопировать ее и вставить, подставив вместо текущих диапазонов ячеек свои: =СУММЕСЛИМН(B2:B25;A2:A25;»Груши»;C2:C25;»>10″). Не забывайте о том, что первый диапазон – то, что вы считаете, далее идет первый критерий – диапазон с названием столбца, потом второй – диапазон с неравенством.

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

Ячейка начинается с определенного значения

=СУММЕСЛИ(диапазон;»текст*»;ранг-сумм)

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

В примере показано, что ячейка G5 содержит эту формулу:

=СУММЕСЛИ(C5:C11; «футболка*»; D5:D11)

Эта формула суммирует суммы в столбце D, когда значение в столбце C начинается с «футболка». Обратите внимание, что СУММЕСЛИ нечувствительна к регистру.

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.

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

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

=СУММЕСЛИМН(Д5:Д11;С5:С11;»футболка*»)

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

Сумма значений между определенными числами

=СУММЕСЛИМН(ранг-сумм;ранг_критериев;»>500″;ранг_критериев;»<1000″)

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

В показанном примере, ячейка F7 содержит эту формулу:

=СУММЕСЛИМН(D5:D11;D5:D11; «>500″;D5:D11;»<1000»)

Эта формула суммирует суммы в столбце D, когда они больше 500 и меньше 1000.

Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.

В этом случае, мы хотим просуммировать значения в столбце D, которые соответствуют двум критериям. Поэтому диапазон суммы — «сумма» (Д4:Д11), а затем два ряда/пары критериев:

сумма;»>500″ // критерии 1
сумма;»<1000″ // критерии 2

С этими критериями, функция СУММЕСЛИМН суммирует все суммы больше 500 и меньше 1000.

Отметим, что оба оператора (>, <) и пороговых значения заключены в двойные кавычки («»).

Если вы хотите включить пороговое значение чисел в сумме (включая суммы, которые равны 500 и 1000), больше или равно (>=) и меньше или равно (<=), вот так:

=СУММЕСЛИМН(сумма;сумма;»>=500″;количество;»<=1000″)

Если вы хотите выставить порог суммы на листе, так что они могут быть легко изменены, используйте эту формулу:

=СУММЕСЛИМН(сумма;сумма;»>=»&А1;сумма;»<«&В1)

где A1-это ссылка на нижний порог и B1-это ссылка на верхний порог

Сумма, если за год

=СУММЕСЛИМН(ранг-суммы;Дата;»>=»&Дата(Год);Дата;»<=»&Дата(год;12;31))

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

В показанном примере, формула в Н6 является:

=СУММЕСЛИМН(сумма;Дата;»>=»&Дата(G6;1;1);даты;»<=»&Дата(G6;12;31))

В результате получается общая сумма за 2011 год. При копировании вниз формулы создается общая сумма для 2012 и 2013 годов.

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

Диапазон суммы в этом примере — это именованный диапазон, называемый «сумма» (Е3:E2931), и критерии поставляются в виде двух пар, как с помощью именованного диапазона под названием «Дата» (Б3:B2931).

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

1. В первый день 2011 года
2. В последний день 2011 года

Эти даты появляются в виде формулы:

Дата; «>=»&Дата(G6;1;1) // Дата >= к 1/1/2011
Дата; «<=»&Дата(G6;12;31) // Дата <= 12/31/2011

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

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

Сумма, если ячейка содержит текст в другой ячейке

=СУММЕСЛИ(диапазон;»*»&А1&»*»;ранг_суммы)

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

В примере показано, ячейка G7 содержит эту формулу:

=СУММЕСЛИ(C5:C11; «*»&F7&»*»;D5:D11)

Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит текст из ячейки F7.

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символов» и так далее.

Так, например, можно использовать такие критерии, как «*шляпа*» для поиска текста в любом месте ячейки.

Когда Excel вычисляет этот аргумент внутри функции СУММЕСЛИ, он будет «видеть» это: «*кофта*».

Обратите внимание, что СУММЕСЛИ нечувствительна к регистру.

Альтернатива с СУММЕСЛИМН

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

=СУММЕСЛИМН(D5:D11;С5:С11;»*кофта*»)

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

Сумма, если ячейки равны

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

В показанном примере, мы подсчитываем все продажи в Западном регионе. Формула в ячейке H4 является:

=СУММЕСЛИ(C5:C14; «Западный»;E5:E14)

Формула в ячейке Н5 является:

=СУММЕСЛИМН(E5:E14;C5:C14;»Западный»)

Обе формулы относятся к названной области диапазонов (С5:C14) и сумм (Е5:Е14).

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

СУММЕСЛИ(диапазон;критерий;ранг_суммы)
СУММЕСЛИМН(ранг_сцммы;диапазон;критерий)

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

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

Сумма, если ячейки не равны

=СУММЕСЛИ(диапазон;»<>значение»;ранг_суммы)

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

В примере показано, ячейка Н5 содержит эту формулу:

=СУММЕСЛИ(C5:C14; «<>Западный»;E5:E14)

Эта формула суммирует суммы в колонке » Е » только тогда, когда регион в столбце C — не «Западный».

Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, включая «не равно», который вводится в качестве <>.

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

Альтернатива с СУММЕСЛИМН

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

=СУММЕСЛИМН(объем; регион;»<>Западный»)

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

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

Допустим нам необходимо суммировать числовые значения в границах дат от 23-го и до 29-го июня включительно. Начальные и конечные даты введены в соответствующих ячейках дополнительной таблицы для составления запросов выборки из исходной таблицы. Ниже на рисунке изображены исходные значения и условия отбора значений для суммирования:

Описанная данная техника суммирования по нескольким условиям с помощью функции СУММЕСЛИ основана на вычитании. В данной формуле первая функция СУММЕСЛИ возвращает сумму значений, соответствующих датам раньше конечной даты в ячейке E3 или равной этой дате (в данном случаи =29-е Июня). Оператор «меньше» объединен символом амперсант (&) со ссылкой на ячейку E2 (начальной даты).

Когда формула содержала бы только первую функцию =СУММЕСЛИ(A2:A12;”<=”&E3;B2:B12), тогда итоговый результат составил = 5721.

Но нам необходимо получить сумму чисел в границах между 25-ым и 29-ым числом Июня месяца включительно, а не между 20-ым и 29-ым. Для этого необходимо вычесть из первого итогового результата 5721,00 сумму чисел раньше даты 25-го Июня – 3 726,50.

Данного результата мы добиваемся с помощью повторного использования такой же функции СУММЕСЛИ, но с немного измененным вторым аргументом СУММЕСЛИ(A2:A12;”<“&E2;B2:B12).

Теперь проверим разницу вычислений двух частей формулы с помощью вычитания:

Суммирование числовых значений, соответствующих датам раньше конечной даты (29-го Июня и равной ей) с последующим вычитанием от данного результата суммы всех числовых значений, относящихся к датам раньше от начальной (25-го Июня) позволяет получить сумму всех чисел в границах соответствующим датам заданного периода в таблице составления запроса выборки.

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

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

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

Формула 1: =СУММЕСЛИ(A2:A8; “=” & F1; C2:C8)

Формула 2: =СУММЕСЛИ(A2:A8; F1; C2:C8)

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

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

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

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

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

Пример 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. Суммирование по заданному количеству символов

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

=СУММЕСЛИ(A2:A8; “??????”; C2:C8)

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

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

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

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

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

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

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

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

=СУММЕСЛИ(A2: A8; “~?”; C2: C8)

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

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

=СУММЕСЛИ(B2:B9; “>=01.11.2017”; C2:C9) – СУММЕСЛИ(B2:B9; “>=01.12.2017”; C2:C9)

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

Задача1 (1 текстовый критерий и 1 числовой)

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

1. = СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;”>=”&E2)

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

• B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
• A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
• D2 и “>=”&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

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

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);–(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :

• Результатом вычисления A2:A13=D2 является массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2:A13=D2 , а затем нажав F9
  
• Результатом вычисления B2:B13 является массив {3:5:11:98:4:8:56:2:4:6:10:11}, т.е. просто значения из столбца B
  
• Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является {0:0:0:0:4:8:56:0:0:0:0:0}. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
• Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив {0:0:1:1:0:1:1:0:0:1:1:1}. Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
• Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

Помогла ли Вам статья?

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

​Смотрите также​ ячейке этого столбца​ продажи у менеджеров​ условию. Эта функция​ периода, данные в​ п.ч. первое слово​ пользовательских предложений для​ см. в статье​ в Excel».​​ будем писать разные​​ С9 устанавливаем функцию​​ искомым символом значок​​в аргументе​Проданное количество​ ЕСЛИ.​ диапазону. А что​ рабочем листе Excel​

Функция СУММ – простое суммирование ячеек в Excel

​В этом уроке мы​​ будет значение 1,​​ с фамилией из​ проще. Читайте о​ котором мы хотим​ в ячейке начинается​ Excel.​ Функции СУММЕСЛИ, СЧЁТЕСЛИ​Ещё примеры этой​ даты. В ячейку​ «СУММЕСЛИ».​ тильды (~).​

​Условие1​Продукт​​СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2;​​ делать, если необходимо​ (отличном от Лист1).​

​ не будем рассматривать,​​ иначе — 0.​​ пяти букв, можно​ ней в статье​ посчитать.​ не с буквы​Рассмотрим​ и СЧИТАТЬПУСТОТЫ возвращают​

​ функции смотрите в​​ E10 устанавливаем функцию​​В​Например, формула =СУММЕСЛИМН(A2:A9; B2:B9;​из поиска исключаются​Продавец​ условие2]; …)​ просуммировать один диапазон,​​Функция​​ как посчитать сумму​ Формула, которую надо​ использовать критерий​ «Функция «СУММЕСЛИ» в​В диалоговом окне​

​ «Я».​​примеры функции «СУММЕСЛИМН» в​​ ошибку #ЗНАЧ!.​ статье «Функция «СУММЕСЛИ»​ «СУММЕСЛИ».​

​Excel функция «СУММЕСЛИ»​ «=Я*»; C2:C9; «Арте?»)​ бананы (​​5​​=СУММЕСЛИМН(A2:A9; B2:B9; «=Я*»; C2:C9;​ а условие проверять​СУММ​ в Excel при​ ввести в этот​?????​

​ Excel».​​ условия функции «СУММЕСЛИМН»​​Чтобы посчитать и​Excel​Функции СУММЕСЛИ и СУММЕСЛИМН​ в Excel, примеры»​В ячейке B10​

​находится на закладке​ будет суммировать все​»<> Бананы»​​Яблоки​​ «Артем»)​ по-другому?​может принимать до​

​ помощи оператора сложения,​ столбец очень простая:​. А чтобы найти все​В Excel есть​

​ по датам написали​​ эти, «красные яблоки»,​​. Как написать условия​ возвращают неправильные результаты,​ здесь. В этой​ пишем нужную дату.​ «Формулы» в разделе​ значения с именем,​). Кроме того, функция​Артем​=СУММЕСЛИМН(A2:A9; B2:B9; «<>Бананы»; C2:C9;​

​На этот случай функция​ 255 аргументов и​ автосуммы и прочих​=(A2=»Копейка»)*(B2=»Григорьев»)​

СУММЕСЛИ — условная сумма в Excel

​ продажи менеджеров, у​​ еще одна функция,​​ так.​ напишем такое условие​ для различных способов​ если они используются​ статье мы рассмотрели,​​Диалоговое окно функции​​ «Библиотека функций». Нажимаем​ начинающимся на «Арте»​

​ выполняет поиск имени​4​ «Артем»)​СУММЕСЛИ​ суммировать сразу несколько​ инструментов. Сегодня мы​

​Логические равенства в скобках​ которых фамилия начинается​ которая сначала умножает​Получилась такая формула.​ функции. «*я*»​

​ выбора данных для​ для сопоставления строк​

​ как использовать символы,​ заполнили так.​ на «Математические» и​ и оканчивающимся любой​»Артем»​Яблоки​

​Имя аргумента​припасла третий необязательный​ несмежных диапазонов или​ разберем всего две​ дают значения ИСТИНА​ на букву «П»,​ ячейки в столбцах,​=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;A2:A8;»Иванов»;D2:D8;»>=»&C11;D2:D8;» Здесь мы​Формула будет такой.​ суммирования, в т.​

​ длиннее 255 символов.​​ для поиска конкретных​​Нажимаем «ОК». Формула получилась​ выбираем функцию «СУММЕСЛИ».​ буквой.​в диапазоне ячеек,​Ольга​Описание​ аргумент, который отвечает​ ячеек:​

​ функции:​ или ЛОЖЬ, что​ а заканчивается на​ затем складывает результаты.​ два раза указали​

​ =СУММЕСЛИМН(D2:D8;C2:C8;»*я*»)​​ ч. с датами.​​Решение:​

​ данных.​ такая. =СУММЕСЛИ(D2:D7;B10;C2:C7)​Появится диалоговое окно​Различия между функциями СУММЕСЛИ​​ заданных аргументом​​15​Диапазон_суммирования​ за диапазон, который​Если среди суммируемых значений​

​СУММ​ для Excel равносильно​ «В» — критерий​ Можно настроить функцию,​ один и тот​​Если нужно, то​​ Как установить​​Сократите строку, если​​Если нужно указать​Третий пример.​ «Аргументы функции». Заполняем​ и СУММЕСЛИМН​Диапазон_условия2​Артишоки​    (обязательный аргумент)​ необходимо просуммировать. Т.е.​ содержится текст, то​и​ 1 и 0.​П*В​ чтобы считала по​ же диапазон дат,​

​ добавляем условия в​

office-guru.ru

Функция СУММЕСЛИМН

​функцию «СУММЕСЛИМН» в Excel​ это возможно. Если​ не одно условие​Сумма ячеек​ его так.​Порядок аргументов в функциях​(C2:C9). Затем функция​Артем​Диапазон ячеек для суммирования.​ по первому аргументу​ функция​СУММЕСЛИ​

​ Таким образом, поскольку​. Строчные и прописные​ условию. Смотрите об​

Синтаксис

​ но указали разные​ функцию СУММЕСЛИМН по​

• ​, какие можно указать​ сократить строку нельзя,​

• ​ суммирования, а несколько,​Excel с условием.​

Примеры

​ в Excel по​ чтобы разделить значение​ функции читайте в​ отделам за все​ отделов, п. ч.​Диапазон_суммирования​​ аргументом​​22​Условие1​В следующем примере мы​ расчет:​​ все возможные вопросы​​ только если оба​​ ячейки, значения которых​​Имеем таблицу по продажам,​

Распространенные неполадки

Рекомендации

​Одинаковое количество строк и​

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

​ аргументов​Ольга​

Помогите нам улучшить Excel

​ суммируются в пределах​ воспользоваться функцией​ на ячейки и​ ячейках. Мы, конечно​ то советую почитать​ не умеет проверять​ «Копейку», например), то​

support.office.com

Функция «СУММЕСЛИ» в Excel.

​ ячейках С11 и​ функции напишем такое.​​ менеджер Васечкин продал​ ​должно указываться одинаковое​​ ошибку (по незнанию​ ячейки в определенных​ но и текст,​ столбцов для аргументов,​Условие1, 2​Формула​
​ аргумента​​СУММЕСЛИМН​ ​ диапазоны, но и​
​ же, можем воспользоваться​ предварительно про них​
​ больше одного критерия.​
​ задача решалась бы​ ​ С12, т.д.​
​ «<>*яблоки*»​ яблок красных, зеленых,​ количество строк и​ или опечатка, т.д.).​ строках».​ символы, адрес отдельной​ задающих диапазоны ячеек​
​заключены в кавычки.​ ​Описание​​Диапазон_суммирования​, которая позволяет осуществлять​ различные математические операторы​ обычным оператором суммирования:​ много хорошего здесь.​
​ Поэтому начиная с​ достаточно легко при​Пример 5.​
​Символы «<>» означают​ других покупателю Иванову.​ столбцов.​ Какие бывают ошибки,​Какими способами можно​ ячейки с номером​Аргумент​Неверный результат возвращается в​
​=СУММЕСЛИМН(A2:A9; B2:B9; «=Я*»; C2:C9;​.​ условное суммирование в​ и даже другие​Но мы также можем​ Ну, а в​ версии Excel 2007​ помощи встроенной функции​Можно в условиях​ – не равно.​В ячейку Н4​В приведенном ниже примере​ что они означают,​ сделать самостоятельно любой​ отдела, код, т.​Диапазон_условия​ том случае, если​
​ «Артем»)​Условие1​ Excel на основе​ функции Excel:​ воспользоваться функцией​ нашем случае задача​ в набор функций​
​ Excel​ функции «СУММЕСЛИМН» указать​
​ О других символах,​ вставляем функцию «СУММЕСЛИМН».​
​ формула должна возвращать​ как их найти​ бланк в Excel,​ д. Какие символы​должен иметь то​ диапазон ячеек, заданный​Суммирует количество продуктов, названия​    (обязательный аргумент)​ нескольких критериев.​Если интересно узнать, как​СУММ​
​ решается одной формулой:​
​ была добавлена функция​
​СУММЕСЛИ (SUMIF)​ ​ и символы «>»​
​ которые можно вставить​ В условии функции​ итоговое значение ежедневных​ и исправить? Чтобы​ смотрите в статье​ можно использовать в​ же количество строк​ аргументом​ которых начинаются с​Условие, определяющее, какие ячейки​Суммирование – это одно​
​ посчитать накопительную сумму​и записать формулу​
​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​СУММЕСЛИМН (SUMIFS)​​ из категории​ и « Получится​
​ в формулу и​
​ напишем «*яблоки*». По​ ​ продаж яблок в​
​ ответить на эти​ «Как сделать бланк​ формуле, что они​ и столбцов, что​Диапазон_суммирования​Я​ суммируются в аргументе​ из основных действий,​
​ в Excel, обратитесь​ следующим образом:​После ввода этой формулы​- в ней​Математические (Math&Trig)​ такая формула.​ что они означают,​ этому условию Excel​
​ Омске. Однако аргумент​ вопросы, читайте статью​ в Excel» здесь.​ означают, смотрите в​ и аргумент​, содержит значение ИСТИНА​
​и которые были​
​Диапазон_условия1​ ​ которое пользователь выполняет​
​ к этому уроку.​Поскольку функция​ необходимо нажать не​ количество условий проверки​. Выделяем пустую ячейку​=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;E2:E8;»>500″)​ смотрите в статье​ будет искать все​диапазон_суммирования (C2:C10)​ «Ошибки в формулах​Четвертый пример.​ статье «Символы в​
​Диапазон_суммирования​ или ЛОЖЬ.​ проданы продавцом​. Например, условия могут​ в Microsoft Excel.​
​Функция​ ​СУММ​​ Enter, как обычно,​ увеличено аж до​ для результата, жмем​Здесь последний критерий​ «Символы в формулах​ яблоки. Диалоговое окно​не соответствует количеству​
​ Excel».​Сумма​ формулах Excel» тут.​.​Значения ИСТИНА и ЛОЖЬ​Артем​ вводится в следующем​
​ Функции​СУММЕСЛИ​поддерживает работу не​ а​ 127! Функция находится​ кнопку​ написали так –​ Excel».​ функции заполнили так.​
​ строк и столбцов​В этом разделе перечислены​Excel, если несколько условий.​В строке «Диапазон​К началу страницы​ в диапазоне ячеек,​. Подстановочный знак (*)​ виде:​СУММ​
​позволяет подсчитать условную​ только с отдельными​Ctrl + Shift +​ в той же​fx​ «>500».​
​Формула будет такая.​Формула получится такая.​ в аргументах​ распространенные причины ошибки​Если нужно посчитать​ суммирования» мы указываем​Задать вопрос на форуме​ заданных аргументом​ в аргументе​32​и​ сумму в Excel,​

excel-office.ru

Как исправить #VALUE! сообщение об ошибке в функции СУММЕСЛИ и СУММЕСЛИМН

​ ячейками, но и​ Enter​ категории​в строке формул,​Пример 6.​ =СУММЕСЛИМН(D2:D8;C2:C8;»<>*яблоки*»)​

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

​=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;A2:A8;»Иванов»;C2:C8;»*яблоки*»)​диапазон условия (A2:A12 и​ #ЗНАЧ! в функциях​ сумму по двум​ диапазон столбца с​ сообщества, посвященном Excel​

​Диапазон_суммирования​​Условие1​,​СУММЕСЛИ​ т.е. сумму ячеек,​ целыми диапазонами, то​- тогда Excel​Математические​ находим функцию​Если посчитать в​Если нужно посчитать​

​Получилось, что всяких​​ B2:B12)​ СУММЕСЛИ и СУММЕСЛИМН​ и более столбцам,​ данными, которые нужно​

​У вас есть предложения​, оцениваются по-разному, что​(​»>32″​созданы, чтобы облегчить​ которые удовлетворяют определенному​ вышеприведенную формулу можно​ воспримет ее как​и работает похожим​

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

​СУММЕСЛИ​ непустых ячейках, то​ не сумму в​ яблок Васечкин продал​. Использование синтаксиса =СУММЕСЛИМН(​

​ и способы их​​ то можно в​ посчитать. У нас​ по улучшению следующей​ может приводить к​»=Я*»​,​ эту задачу и​ критерию. Функция​

​ модифицировать:​ формулу массива и​

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

​ образом, но имеет​в списке:​ ставим последний критерий»​ выбранных ячейках, а​​ Иванову на сумму​​C2:C10​​ устранения.​​ формулу вставить несколько​ – это столбец​ версии Excel? Если​

​ непредвиденным результатам при​) используется для поиска​B4​ дать пользователям максимально​СУММЕСЛИ​​Истинная мощь функции​​ сам добавит фигурные​ больше аргументов:​Жмем​​ — «>0». Формула​ количество ячеек по​​ 7897 рублей.​​;​​Если функция СУММЕСЛИ или​​ функций «СУММЕСЛИ». Например,​​ «Сумма».​​ да, ознакомьтесь с​​ их суммировании.​ соответствующих названий продуктов​

​,​ удобный инструмент. Надеюсь,​может содержать только​СУММ​

​ скобки. Вводить скобки​​При помощи полосы прокрутки​ОК​​ получится такая.​​ определенному условию, то​Пример 2.​

​A2:A12​​ СУММЕСЛИМН ссылается на​ в нашу таблицу​Заполнили диалоговое окно​

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

​ темами на портале​Ячейки в аргументе​

Помогите нам улучшить Excel

​ в диапазоне ячеек,​»яблоки»​ что этот урок​ один критерий.​раскрывается, когда необходимо​ с клавиатуры не​ в правой части​

support.office.com

Примеры функции «СУММЕСЛИМН» в Excel.

​ и вводим ее аргументы:​​=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;E2:E8;»>0″)​ ​ воспользуйтесь функцией «СЧЕТЕСЛИ».​​Нужно посчитать, сколько​;A14;​ ячейку или диапазон​ из примера добавим​ так.​ пользовательских предложений для​​Диапазон_суммирования​​ заданных аргументом​или​ помог Вам освоить​Например, следующая формула суммируем​ сложить большое количество​
​ надо. Легко сообразить,​
​ окна можно задать​Диапазон​Пример 7.​ Подробнее об этой​ продано всех фруктов​B2:B12​ в закрытой книге,​
​ столбец В с​
​Нажимаем «ОК». Получилась такая​ Excel.​, которые содержат значение​Диапазон_условия1​
​»32″​ базовые функции суммирования​ только положительные числа​ ячеек в Excel.​ что этот способ​ и третью пару​- это те​Нужно посчитать продажи​
​ функции читайте в​
​ на букву «Я».​
​;B14) приведет к ошибке​ это приводит к​ новыми данными. Формулу​ формула. =СУММЕСЛИ(A2:A7;1;C2:C7)​
​Рассмотрим одну из​
​ ИСТИНА, возвращают 1,​(B2:B9). Кроме того,​.​ в Excel, и​ диапазона A1:A10. Обратите​ В примере ниже​ (как и предыдущий)​
​ (​ ячейки, которые мы​ Васечкиным по всем​ статье «Функция «СЧЕТЕСЛИ»​​ В условиях функции​ #ЗНАЧ!.​​ ошибке #ЗНАЧ!.​​ в ячейке напишем​​Мы рассмотрели этот​​ популярных​ тогда как ячейки,​ функция выполняет поиск​Диапазон_условия2, Условие2, …​
​ теперь Вы сможете​ внимание, что условие​ требуется просуммировать 12​ легко масштабируется на​Диапазон_условия3​ проверяем на выполнение​ филиалам конкретного магазина.​ в Excel».​ «СУММЕСЛИМН» напишем так.​
​В функции СУММЕСЛИМН​Примечание.​ такую. =СУММЕСЛИ(A2:A7;B10;C2:C7)+СУММЕСЛИ(A2:A7;B10;D2:D7)​ вариант установки формулы​
​функций в​ которые содержат значение​
​ имени​    (необязательный аргумент)​ свободно применять эти​ заключено в двойные​
​ значений. Функция​
​ три, четыре и​-​Критерия​ Таблица такая.​Пример 4.​​ «=я*» Вся формула​ аргумент диапазон_условия должен​Это известная проблема​Этой формулой мы​ в ячейку, чтобы​Excel «СУММЕСЛИ»​ ЛОЖЬ, возвращают 0​»Артем»​Дополнительные диапазоны и условия​
​ знания на практике.​ кавычки.​СУММ​ т.д. условий без​Условие3​. В нашем случае​В условиях функции напишем​Функция «СУММЕСЛИМН» в​ будет такая. =СУММЕСЛИМН(D2:D8;C2:C8;»=я*»)​
​ совпадать с аргументом​ с рядом других​
​ считаем выборочные данные​ понять принцип работы​. Эта функция выбирает​ (нуль).​в диапазоне ячеек,​ для них. Можно​ Удачи Вам и​В качестве условия можно​позволяет сделать это​ каких-либо ограничений.​
​), и четвертую, и​
​ — это диапазон​ ​ так — «*Монетка*».​​Excel с датами​
​Этой формулой посчитаются​ диапазон_суммирования​ функций Excel, в​ из столбцов C​ функции «СУММЕСЛИ». Когда​
​ и складывает данные​Действие​ заданных аргументом​ ввести до 127 пар​ успехов в изучении​ использовать значение ячейки.​
​ несколькими щелчками мышью,​В категории​ т.д. — при​
​ с фамилиями менеджеров​ Формула получится такая.​.​ все слова в​Решение:​
​ том числе СЧЁТЕСЛИ,​ и D по​ поймем принцип составления​ из таблицы по​​Описание​
​Диапазон_условия2​ диапазонов и условий.​ Excel!​ В этом случае,​ если же использовать​Базы данных (Database)​ необходимости.​ продаж.​=СУММЕСЛИМН(D2:D8;B2:B8;»Васечкин»;A2:A8;»*Монетка*»)​Нужно сложить данные​ столбце, которые начинаются​В приведенном примере​
​ СЧЁТЕСЛИМН и СЧИТАТЬПУСТОТЫ.​
​ первому отделу.​ формулы с функцией​ определенным условиям, которые​Использование подстановочных знаков​​(C2:C9). Затем функция​Чтобы использовать эти примеры​Автор: Антон Андронов​ поменяв условие, изменится​
​ оператор сложения, то​
​ можно найти функцию​Если же у вас​Критерий​Последним условием мы​ из таблицы по​
​ на букву «Я».​
​ измените​ Дополнительные сведения см.​Функцией «СУММЕСЛИ» в​
​ «СУММЕСЛИ», можно формулу​
​ мы пишем в​Подстановочные знаки, такие как​ суммирует соответствующие обоим​ в Excel, выделите​Функция СУММЕСЛИМН — одна из​
​ и результат:​
​ провозиться придется долго.​
​БДСУММ (DSUM)​ пока еще старая​- это то,​ написали «*Монетка*». Слово​​ нескольким условиям, в​Если нужно посчитать, что​диапазон_суммирования​
​ в статье Функции​
​Excel​ написать, сразу, в​ формуле.​ вопросительный знак (?)​ условиям значения в​ нужные данные в​ математических и тригонометрических​Меняем условие, меняется и​В следующем примере функция​, которая тоже способна​ версия Excel 2003,​ что мы ищем​
​ «Монетка» заключили в​ т. ч. по​ продал​на C2:C12 и​ СУММЕСЛИ, СЧЁТЕСЛИ и​можно посчитать, только,​ ячейке, не вызывая​Разберем​
​ или звездочка (*),​ диапазоне ячеек, заданном​ таблице, щелкните их​ функций, которая суммирует​ результат:​СУММ​ решить нашу задачу.​ но задачу с​ в предыдущем указанном​ звездочки. Это значит,​ датам.​

excel-office.ru

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

​именно​ выполните формулу повторно.​

​ СЧИТАТЬПУСТОТЫ возвращают ошибку​​ отрицательные или, только,​ окно функции.​примеры функции «СУММЕСЛИ» в​

Способ 1. Функция СУММЕСЛИ, когда одно условие

​ в аргументах​ аргументом​ правой кнопкой мыши​ все аргументы, удовлетворяющие​Условие можно комбинировать, используя​складывает весь столбец​ Нюанс состоит в​ несколькими условиями решить​ диапазоне. Разрешается использовать​ что Excel будет​​Удобно писать даты​​Васечкин и​​Примечание:​​ #ЗНАЧ!.​ положительные числа. Об​Получились такие данные.​​Excel».​​Условие1, 2​Диапазон_суммирования​​ и выберите команду​​ нескольким условиям. Например,​

​ оператор конкатенации. В​​ А, а это​​ том, что для​

• ​ нужно, то придется​​ символы * (звездочка)​ искать все слова​ в отдельных ячейках.​​именно​​  В функции СУММЕСЛИ​Решение:​ этом способе, читайте​Второй пример.​
• ​У нас такая​​можно использовать для​(A2:A9). Результат — 20.​Копировать​ с помощью этой​ примере ниже формула​ 1048576 значений:​ работы этой функции​ извращаться — см.​ и  ? (вопросительный​ «Монетка» с разными​ Так сможем посчитать​Иванову, то добавим​ можно использовать диапазоны​Откройте книгу, указанную​ в статье «Сумма​Пример функции «СУММЕСЛИ» в​ таблица.​ поиска сходных, но​​=СУММЕСЛИМН(A2:A9; B2:B9; «<>Бананы»; C2:C9;​​. На новом листе​ функции можно найти​ вернет сумму значений,​Следующая формула подсчитывает сумму​ необходимо создать на​ следующие способы.​​ знак) как маски​​ номерами и буквами,​ данные любого периода,​
• ​ эти условия в​​ разного размера.​ в формуле, и​ отрицательных чисел в​Excel по дате.​Первый пример.​

Способ 2. Функция СУММЕСЛИМН, когда условий много

​ не совпадающих значений.​ «Артем»)​ щелкните правой кнопкой​ число всех розничных​ которые больше значения​​ всех ячеек, содержащихся​​ листе специальный диапазон​Добавим к нашей таблице​ или символы подстановки.​ разные филиалы магазина​ изменив даты в​ функцию, как рассмотрели​Задать вопрос на форуме​​ нажмите клавишу F9,​​ Excel».​Теперь посчитаем сумму​Сумма по условию​Вопросительный знак соответствует любому​Суммирует количество продуктов, которые​ мыши ячейку A1​​ продавцов, (1) проживающих​​ в ячейке B1.​ на рабочем листе​ критериев — ячейки, содержащие​

​ еще один столбец,​ Звездочка подменяет собой​ «Монетка». Смотрите статью​ этих отдельных ячейках.​ это выше.​​ сообщества, посвященном Excel​​ чтобы обновить формулу.​​Чтобы сложить и​​ за определенные даты.​Excel.​ отдельно взятому символу,​

​ не являются бананами​ и в разделе​ в одном регионе,​Во всех примерах, приведенных​Лист1​ условия отбора — и​ который будет служить​ любое количество любых​

Способ 3. Столбец-индикатор

​ «Подстановочные знаки в​В ячейке С11​Но, у нас​У вас есть предложения​Также можно обойти эту​ удалить дубли в​ Можно в формуле​Нам нужно посчитать​ звездочка — любой последовательности​ и которые были​Параметры вставки​ (2) чей доход​ ранее, мы производили​

​. Чтобы данная формула​

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

Способ 4. Волшебная формула массива

​ не вызвала циклической​ диапазон функции как​ заказ был в​ — один любой​Есть ещё одна​ периода.​ строка «Красные яблоки».​ версии Excel? Если​ СУММ и ЕСЛИ​ функцию «СУММЕСЛИ». Смотрите​

​ А можно в​

​ продал продукты первый​ найти именно вопросительный​ имени Артем. С​Использовать форматы конечных ячеек​​Это видео — часть учебного​ условие по одному​​ ошибки, ее необходимо​ аргумент:​ «Копейку» и от​ символ. Так, например,​ функция в Excel,​В ячейке С12​ Они по этой​ да, ознакомьтесь с​ вместе в формуле​ статью «Как сложить​ формуле указать адрес​ отдел за все​ знак или звездочку,​

Способ 4. Функция баз данных БДСУММ

​ помощью оператора​​.​​ курса Усложненные функции​​ и тому же​​ использовать на другом​=БДСУММ(A1:D26;D1;F1:G2)​ Григорьева, то в​ чтобы найти все​ которая считает по​ напишем дату окончания​ формуле не посчитаются,​ темами на портале​ массива. Дополнительные сведения​ и удалить дубли​ ячейки, в которой​ дни. В ячейке​

​ нужно ввести перед​

planetaexcel.ru

​<>​