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

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

В этом руководстве мы кратко рассмотрим синтаксис функции и общее использование, а затем применим эти знания на практике с помощью нескольких примеров.

Синтаксис

Как видите, функция СУММЕСЛИ имеет 3 аргумента: первые 2 обязательны, а третий — необязательный.

• Диапазон – это диапазон ячеек для оценки по критериям, например A1: A10.
• Критерий: условие, которое должно быть выполнено. Критерии могут быть представлены в форме числа, текста, даты, логического выражения, ссылки на ячейку или другой функции Excel. Например, вы можете ввести такие критерии, как «3», «текст», «08.12.2020», «<3», «A1» и т. Д. Обратите внимание, что все текстовые критерии или критерии, содержащие математические символы, должны быть заключены в кавычки («), а числовые критерии не требуют кавычек.
• Диапазон-Суммирования: Ячейки для суммирования, если условие выполнено. Этот аргумент является необязательным, и вы должны использовать его только в том случае, если вы хотите назначить ячейки, отличные от тех, которые определены в аргументе диапазона. Если аргумент диапазон_суммирования опущен, Excel будет суммировать те же ячейки, к которым применяются критерии (то есть те, которые указаны в аргументе диапазона).

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

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

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

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

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

• Пример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 букв, используйте следующую формулу:

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

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

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

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