Функция Excel SUMIF

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

• Пример 1: Суммирование, если значения больше, меньше, равны или используют другие операторы сравнения
• Пример 2: Формулы СУММЕСЛИ с текстовыми критериями
• Пример 3: Формулы СУММЕСЛИ со спецсимволами (подстановочными знаками)
• Пример 4: Суммирование значений ячеек, соответствующих пустым или непустым ячейкам
• Пример 5: СУММЕСЛИ с датами в Excel
• Пример 6: Суммирование значений в нескольких столбцах в Excel

 Синтаксис:

Синтаксис функции СУММЕСЛИ (SUMIF) в Excel:

 Аргументы:

• rangeОбязательно. Диапазон ячеек, к которому применяется условие.
• criteriaОбязательно. Критерий, по которому определяются ячейки для суммирования. Критерий может представлять собой текст, число, дату, логическое выражение, ссылку на ячейку или другую функцию.
• Примечание: Любой текстовый критерий или критерий, содержащий математические символы, необходимо заключать в двойные кавычки. Числовые критерии в кавычках не нуждаются.
• sum_range: Необязательный параметр. Указывает диапазон ячеек, которые необходимо просуммировать. Если он не задан, используется диапазон из аргумента диапазон_суммирования.

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

Возвращает сумму чисел из списка по заданному критерию.

 Примеры:

Пример 1: Суммирование, если значения больше, меньше, равны или используют другие операторы сравнения

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

Допустим, у вас есть таблица с колонками «Имя», «Количество» и «Цена», как показано на скриншоте ниже. Как рассчитать общую стоимость товаров, у которых количество превышает 250?

Примените приведённую ниже формулу в пустую ячейку:

• Советы: В приведённой выше формуле:
• B2:B8: диапазон ячеек, в котором выполняется поиск по заданному критерию.
• ">250" — условие, по которому следует суммировать значения ячеек.
• C2:C8: указывает диапазон, который нужно просуммировать.

Затем нажмите клавишу Enter, чтобы получить общую цену, где количество больше 250, как показано на следующем скриншоте:

Примечание: Вы можете использовать и другие операторы сравнения — например, «больше или равно» (>=), «меньше чем» (<) и т.д. — в зависимости от ваших задач.

Пример 2: Формулы СУММЕСЛИ с текстовыми критериями

Если вам нужно суммировать значения ячеек на основе наличия определённого текста в другом столбце, функция СУММЕСЛИ (SUMIF) — именно то, что нужно!

Случай 1: Суммирование значений ячеек, если в другом столбце содержится определённый текст

Возьмём для примера приведённые ниже данные: получите общее количество, если имя в столбце A — Дэвид.

Введите или скопируйте приведённую ниже формулу в пустую ячейку:

• Советы: В приведённой выше формуле:
• A2:A8: диапазон ячеек, в котором выполняется поиск по заданному критерию.
• «David»: условие, по которому нужно суммировать значения ячеек.
• B2:B8: диапазон ячеек, которые нужно просуммировать.

Примечание: Вместо условия в приведённой выше формуле вы также можете использовать ссылку на ячейку, например так: =SUMIF(A2:A8, D2, B2:B8).

Случай 2: Суммирование значений ячеек, не равных определённому значению

Как суммировать ячейки, значения которых не равны определённому значению (например, всё количество, кроме David)?

Примените приведённую ниже формулу в пустую ячейку:

• Советы: В приведённой выше формуле:
• A2:A8: диапазон ячеек, в котором выполняется поиск по заданному критерию.
• «David»: условие, по которому необходимо суммировать значения ячеек.
• B2:B8: диапазон ячеек, которые нужно просуммировать.

Примечание: Вы также можете использовать ссылку на ячейку вместо условия в приведённой выше формуле следующим образом: =SUMIF(A2:A8,«<,>,»&,D2, B2:B8). Операторы сравнения необходимо заключать в двойные кавычки («») и соединять со ссылкой на ячейку с помощью амперсанда (&). См. скриншот:

Пример 3: Формулы СУММЕСЛИ со спецсимволами (подстановочными знаками)

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

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

Вопросительный знак (?): Представляет любой один символ в определённой позиции

Случай 1: Суммирование значений на основе частичного совпадения со звёздочкой (*)

Например, у меня есть таблица, как показано на следующем скриншоте. Сейчас я хочу получить общий объём заказов продукта, название которого содержит «Kutools».

В этом случае примените следующую формулу в пустую ячейку:

Затем нажмите клавишу Enter, чтобы получить результат, показанный ниже:

Примечания:

1. Чтобы просуммировать значения ячеек, начинающихся с «Kutools», используйте эту формулу: =SUMIF(A2:A8, «Kutools*», B2:B8).

2. Чтобы суммировать значения ячеек, если соответствующая ячейка в столбце A заканчивается на «Kutools», используйте эту формулу:=SUMIF(A2:A8, «*Kutools», B2:B8).

3. Вы также можете использовать ссылку на ячейку вместо условия в приведённой выше формуле следующим образом: =SUMIF(A2:A8, «*»&,D2&,«*», B2:B8). Обязательно заключите звёздочку в двойные кавычки («») и используйте амперсанд (&), чтобы объединить её со ссылкой на ячейку. См. скриншот:

Случай 2: Суммирование значений с определённым количеством символов с помощью вопросительного знака (?)

Как в Excel просуммировать значения ячеек из списка, где соответствующая ячейка содержит ровно 5 символов, как показано на скриншоте?

Чтобы выполнить эту задачу, примените приведённую ниже формулу:

Совет: В этой формуле один вопросительный знак соответствует одному символу, а пять вопросительных знаков — строке длиной в 5 символов.

Затем нажмите клавишу Enter, и вы получите нужный результат:

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

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

Случай 1: суммирование значений ячеек, если соседняя ячейка пустая

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

Затем нажмите клавишу Enter, чтобы получить итоговую сумму, если в столбце A есть пустые ячейки (см. снимок экрана):

Случай 2: суммирование значений ячеек, если соседняя ячейка не пустая

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

Затем нажмите клавишу Enter, и все значения, у которых соседние ячейки содержат данные, автоматически просуммируются (см. снимок экрана):

Пример 5: функция СУММЕСЛИ с датами в Excel

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

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

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

Затем нажмите клавишу Enter — и все ячейки, соответствующие сегодняшней дате, будут просуммированы (см. снимок экрана):

Примечания:

1. Чтобы просуммировать значения, дата которых меньше или равна сегодняшней, используйте эту формулу:=SUMIF(B2:B8, «<,»&,TODAY(), C2:C8).

2. Чтобы просуммировать значения, дата которых больше или равна сегодняшней, используйте эту формулу:=SUMIF(B2:B8, «>,»&,TODAY(), C2:C8).

Случай 2: суммирование значений между двумя заданными датами

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

Используйте следующую формулу:

Затем нажмите клавишу Enter — и общая сумма за указанный период будет автоматически рассчитана (см. снимок экрана).

Примечание: вместо условия в приведённой выше формуле вы также можете использовать ссылку на ячейку, как показано ниже:=SUMIF(B2:B8, «>,=»&,E2, C2:C8) – SUMIF(B2:B8, ">="&F2, C2:C8) (см. снимок экрана):

Пример 6: суммирование значений в нескольких столбцах в Excel

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

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

Примените приведённую ниже формулу в пустую ячейку:

Затем нажмите клавишу Enter, чтобы получить нужный результат (см. снимок экрана):

Примечание: приведённая выше функция СУММЕСЛИ может оказаться неудобной при суммировании большого количества столбцов. В таком случае на помощь придёт простая и эффективная функция СУММПРОИЗВ. Используйте следующую формулу:=SUMPRODUCT((A2:A8=G2)*(B2:E8)), а затем нажмите клавишу Enter, чтобы получить результат:

• Советы: В приведённой выше формуле:
• A2:A8: диапазон ячеек, в котором выполняется поиск по критерию.
• G2: условие, по которому нужно суммировать значения ячеек.
• B2:E8: диапазон ячеек, которые нужно просуммировать.

 Дополнительные статьи о функции СУММЕСЛИ: