Как использовать функцию СУММЕСЛИ в Excel -

Содержание

Резюме

Функция СУММЕСЛИ в Excel возвращает сумму ячеек, соответствующих одному условию. Критерии можно применять к датам, числам и тексту. Функция СУММЕСЛИ поддерживает логические операторы (>, <,, =) и символы подстановки (*,?) Для частичного сопоставления.

Цель

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

Возвращаемое значение

Сумма предоставленных значений.

Синтаксис

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

Аргументы

  • диапазон - диапазон ячеек, к которому вы хотите применить критерии.
  • критерии - критерии, используемые для определения добавляемых ячеек.
  • диапазон_суммы - (необязательно) Ячейки для сложения. Если сумма_диапазона опущена, вместо этого ячейки в диапазоне суммируются.

Версия

Excel 2003

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

Функция СУММЕСЛИ возвращает сумму ячеек в диапазоне, удовлетворяющем одному условию. Первый аргумент - это диапазон, к которому будут применяться критерии, второй аргумент - это критерии, а последний аргумент - это диапазон, содержащий значения для суммирования. СУММЕСЛИ поддерживает логические операторы (>, <,, =) и подстановочные знаки (*,?) Для частичного сопоставления. В критериях может использоваться значение из другой ячейки, как описано ниже.

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

СУММЕСЛИ поддерживает только одно условие. Если вам нужно применить несколько критериев, используйте функцию СУММЕСЛИМН. Если вам нужно манипулировать значениями, которые появляются в аргументе диапазона (т.е. извлекать год из дат для использования в критериях), см. Функции СУММПРОИЗВ и / или ФИЛЬТР.

Основное использование | Критерии в другой ячейке | Не равно | Пустые ячейки | Даты | Подстановочные знаки | Ролики

Основное использование

На показанном листе есть три примера СУММЕСЛИ. В первом примере (G6) СУММЕСЛИ настроена на суммирование значений, превышающих 100. Во втором примере (G7) СУММЕСЛИ возвращает сумму значений, у которых цвет является «красным». В последнем примере (G8) СУММЕСЛИ настроена для суммирования значений, где состояние - «CA» (Калифорния).

=SUMIF(D6:D10,">100") // values> 100 =SUMIF(B6:B10,"Jim",D6:D10) // Rep = Jim =SUMIF(C6:C10,"ca",D6:D10) // State = CA

Обратите внимание, что знак равенства (=) не требуется при построении критерия «равно». Также обратите внимание, что СУММЕСЛИ не чувствительна к регистру. Вы можете суммировать значения, где Rep - Джим, используя «jim» или «Jim».

Критерии в другой ячейке

Значение из другой ячейки может быть включено в критерии с помощью конкатенации. В приведенном ниже примере СУММЕСЛИ возвращает сумму всех продаж сверх значения в G4. Обратите внимание, что оператор "больше" (>), который является текстом, должен быть заключен в кавычки. Формула в G5:

=SUMIF(D5:D9,">"&G4) // sum if greater than G4

Не равно

Чтобы выразить критерий "не равно", используйте оператор "" в двойных кавычках (""):

=SUMIF(B5:B9,"red",C5:C9) // not equal to "red" =SUMIF(B5:B9,"blue",C5:C9) // not equal to "blue" =SUMIF(B5:B9,""&E7,C5:C9) // not equal to E7

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

Пустые ячейки

СУММЕСЛИ может вычислять суммы на основе пустых или непустых ячеек. В приведенном ниже примере СУММЕСЛИ используется для суммирования сумм в столбце C в зависимости от того, содержит ли столбец D «x» или он пуст:

=SUMIF(D5:D9,"",C5:C9) // blank =SUMIF(D5:D9,"",C5:C9) // not blank

Даты

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

=SUMIF(B5:B9,"="&DATE(2019,4,1),C5:C9) =SUMIF(B5:B9,">"&E9,C5:C9)

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

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

Функция СУММЕСЛИ поддерживает подстановочные знаки, как показано в примере ниже:

=SUMIF(B5:B9,"mi*",C5:C9) // begins with "mi" =SUMIF(B5:B9,"*ota",C5:C9) // ends with "ota" =SUMIF(B5:B9,"????",C5:C9) // contains 4 characters

Ниже приведены дополнительные примеры формул СУММЕСЛИ.

Заметки

  • СУММЕСЛИ поддерживает только одно условие. Используйте функцию СУММЕСЛИМН для нескольких критериев.
  • When sum_range is omitted, the cells in range will be summed.
  • Text strings in criteria must be enclosed in double quotes (""), i.e. "apple", ">32", "ja*"
  • Cell references in criteria are not enclosed in quotes, i.e. "<"&A1
  • The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).
  • To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
  • SUMIFS requires a range, you can't substitute an array.

Related videos

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

Интересные статьи...