СУММЕСЛИ в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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