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

1

Общая формула СУММЕСЛИ в Excel:

=SUMIF(condition_range,condition,sum range)

Давайте рассмотрим пример. Но теория… Ах! Мы расскажем об этом позже.

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

2

На основании этих данных нам необходимо ответить на следующие вопросы:

3

Начнем с первого вопроса.

СУММЕСЛИ с текстовым условием. Нам нужно указать сумму выигрыша, выигранного Рави.

Таким образом, нашим диапазоном условий будет диапазон имен, то есть A2: A10. Наше состояние — Рави, а диапазон сумм — E2: E10.

Итак, в ячейке I2 мы напишем:

=SUMIF(A2:A10,"ravi",E2:E10)

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

Обратите внимание, что ravi пишется мелким шрифтом. Поскольку СУММЕСЛИ не чувствительна к регистру, это не имеет значения.

Приведенная выше формула СУММЕСЛИ вернет 31907, как показано на изображении ниже.

4

СУММЕСЛИ с логическими операторами. Что касается второго вопроса, наш диапазон условий будет D2: D10. Условие> 70 и диапазон сумм такой же, как и раньше.

=SUMIF(D2:D10,">70",E2:E10)

Приведенная выше формула СУММЕСЛИ вернет 103973, как показано на рисунке ниже.

5

СУММЕСЛИ с операторами подстановочных знаков В третьем вопросе наше условие — Дели. Но у нас нет городской колонки. Хммм… Так что у нас? Ага! Код города. Это сработает.

Мы знаем, что все коды городов Дели начинаются с 1100. Коды городов состоят из 6 цифр. Итак, мы знаем, что это 1100 фунтов стерлингов. «?» Оператор используется, когда мы знаем количество символов, но не знаем их. Поскольку здесь мы знаем, что после 1100 есть еще два числа. Они могут быть любыми, поэтому мы использовали «?». Если бы мы не знали количество символов, мы бы использовали «*».

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

Вы можете объединять числа с помощью «», чтобы сделать их текстовыми значениями.

(formula to convert number into text) = number & “” or =CONCATENATE(number,””)

Теперь в ячейке I2 напишите эту формулу

=SUMIF(B2:B10,"1100??",E2:E10)

Это вернет сумму цены, код города которой начинается с 1100.

В нашем примере это 79836.

6

Примечания к профессионалам:

Если диапазон сумм и диапазон условий совпадают, вы можете опустить переменную sum_range в функции СУММЕСЛИ. = СУММЕСЛИ (E2: E10, «> 15000») и = СУММЕСЛИ (E2: E10, «> 15000», E2: E10) * даст тот же результат, 56163.

  • Текстовые значения заключены в двойные кавычки, а числа — нет.

СУММЕСЛИ (C2: C10,103, E2: E10) это будет работать нормально и вернет 28026.

Однако при работе с логическими операторами необходимо использовать двойные кавычки. Как в нашем примере = СУММЕСЛИ (D2: D10, «> 70», E2: E10) * Он может проверять только одно условие. Для нескольких условий мы используем функцию СУММЕСЛИМН в Excel.

Вопрос: У меня есть данные о продажах, которые содержат значение месяца, региона и продаж. Теперь я хочу в Excel вычислить следующее:

Продажи за определенный месяц для всех регионов. Общий объем продаж за исключением определенного месяца * Продажи за определенный месяц и регион. Ниже приведен снимок данных:

image 1

  • Для расчета продаж за январь месяц для всех регионов; формула в ячейке F2:

* = СУММЕСЛИ (A2: A10; «Янв»; C2: C10)

image 2

  • Чтобы получить общий объем продаж, кроме января, т.е. мы можем использовать СУММЕСЛИ без знака (<>); формула в ячейке F3:

* = СУММЕСЛИ (A2: A10; «<> янв», C2: C10)

image 3

  • Чтобы получить общий объем продаж, используя критерий ИЛИ; формула в ячейке F4:

* = СУММЕСЛИ (A2: A10, «Фев», C2: C10) + СУММЕСЛИ (A2: A10, «Мар», C2: C10)

image 4

  • Чтобы получить Продажи за определенный месяц и регион; формула в ячейке F5:

\ {= СУММ ((A2: A10 = «Янв») (B2: B10 = «Север») * C2: C10)}

Примечание. Это формула массива. Используйте одновременно клавиши CTRL + SHIFT + ENTER

image 5

xlsx-1567