Ячейка начинается с определенного значения
=СУММЕСЛИ(диапазон;»текст*»;ранг-сумм)
Для суммирования ячеек, если другие ячейки начинаются с определенного значения, можно использовать функцию СУММЕСЛИ.
В примере показано, что ячейка G5 содержит эту формулу:
=СУММЕСЛИ(C5:C11; «футболка*»; D5:D11)
Эта формула суммирует суммы в столбце D, когда значение в столбце C начинается с «футболка». Обратите внимание, что СУММЕСЛИ нечувствительна к регистру.
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.
Чтобы соответствовали все предметы, которые начинаются на «футболка», нужны критерии «футболка*». Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).
Вы также можете использовать функцию СУММЕСЛИМН для суммирования, если ячейки начинают с. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
=СУММЕСЛИМН(Д5:Д11;С5:С11;»футболка*»)
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.
Сумма значений между определенными числами
=СУММЕСЛИМН(ранг-сумм;ранг_критериев;»>500″;ранг_критериев;»<1000″)
В сумме, если между можно использовать СУММЕСЛИМН по двум критериям.
В показанном примере, ячейка F7 содержит эту формулу:
=СУММЕСЛИМН(D5:D11;D5:D11; «>500″;D5:D11;»<1000»)
Эта формула суммирует суммы в столбце D, когда они больше 500 и меньше 1000.
Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.
В этом случае, мы хотим просуммировать значения в столбце D, которые соответствуют двум критериям. Поэтому диапазон суммы — «сумма» (Д4:Д11), а затем два ряда/пары критериев:
сумма;»>500″ // критерии 1
сумма;»<1000″ // критерии 2
С этими критериями, функция СУММЕСЛИМН суммирует все суммы больше 500 и меньше 1000.
Отметим, что оба оператора (>, <) и пороговых значения заключены в двойные кавычки («»).
Если вы хотите включить пороговое значение чисел в сумме (включая суммы, которые равны 500 и 1000), больше или равно (>=) и меньше или равно (<=), вот так:
=СУММЕСЛИМН(сумма;сумма;»>=500″;количество;»<=1000″)
Если вы хотите выставить порог суммы на листе, так что они могут быть легко изменены, используйте эту формулу:
=СУММЕСЛИМН(сумма;сумма;»>=»&А1;сумма;»<«&В1)
где A1-это ссылка на нижний порог и B1-это ссылка на верхний порог
Сумма, если за год
=СУММЕСЛИМН(ранг-суммы;Дата;»>=»&Дата(Год);Дата;»<=»&Дата(год;12;31))
Если Вам необходимо суммировать, если за год, можно использовать функцию СУММЕСЛИМН по двум критериям.
В показанном примере, формула в Н6 является:
=СУММЕСЛИМН(сумма;Дата;»>=»&Дата(G6;1;1);даты;»<=»&Дата(G6;12;31))
В результате получается общая сумма за 2011 год. При копировании вниз формулы создается общая сумма для 2012 и 2013 годов.
Первый аргумент для функции СУММЕСЛИМН всегда суммируемый диапазон («ранг_суммы»), и критерии поставляются в виде одного или нескольких диапазона/пары критериев.
Диапазон суммы в этом примере — это именованный диапазон, называемый «сумма» (Е3:E2931), и критерии поставляются в виде двух пар, как с помощью именованного диапазона под названием «Дата» (Б3:B2931).
Каждый раз функция ДАТА используется в критериях для построения двух допустимых дат, как с помощью того же года:
- В первый день 2011 года
- В последний день 2011 года
Эти даты появляются в виде формулы:
Дата; «>=»&Дата(G6;1;1) // Дата >= к 1/1/2011
Дата; «<=»&Дата(G6;12;31) // Дата <= 12/31/2011
В результате, формула рассчитывает общую сумму всех сумм только в 2011 году.
Поскольку мы используем ссылку на ячейку, чтобы поставить год, формула может быть скопирована вниз, чтобы также создать итог на 2012 и 2013 годы.
Сумма, если ячейка содержит текст в другой ячейке
=СУММЕСЛИ(диапазон;»*»&А1&»*»;ранг_суммы)
Чтобы суммировать ячейки,которые содержат определенный текст в другой ячейке, вы можете использовать функцию СУММЕСЛИ с подстановкой и конъюнкцией.
В примере показано, ячейка G7 содержит эту формулу:
=СУММЕСЛИ(C5:C11; «*»&F7&»*»;D5:D11)
Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит текст из ячейки F7.
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символов» и так далее.
Так, например, можно использовать такие критерии, как «*шляпа*» для поиска текста в любом месте ячейки.
Когда Excel вычисляет этот аргумент внутри функции СУММЕСЛИ, он будет «видеть» это: «*кофта*».
Обратите внимание, что СУММЕСЛИ нечувствительна к регистру.
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
=СУММЕСЛИМН(D5:D11;С5:С11;»*кофта*»)
Обратите внимание, что диапазон суммы всегда стоит на первом месте в функции СУММЕСЛИМН.
Сумма, если ячейки равны
Если вам нужно сложить числа на основе других ячеек равных определенному значению, вы можете легко это сделать с помощью функции СУММЕСЛИ или функции СУММЕСЛИМН.
В показанном примере, мы подсчитываем все продажи в Западном регионе. Формула в ячейке H4 является:
=СУММЕСЛИ(C5:C14; «Западный»;E5:E14)
Формула в ячейке Н5 является:
=СУММЕСЛИМН(E5:E14;C5:C14;»Западный»)
Обе формулы относятся к названной области диапазонов (С5:C14) и сумм (Е5:Е14).
Обе формулы используют встроенные функции для расчета промежуточных итогов, но синтаксис функции СУММЕСЛИ и СУММЕСЛИМН немного отличается:
СУММЕСЛИ(диапазон;критерий;ранг_суммы)
СУММЕСЛИМН(ранг_сцммы;диапазон;критерий)
В обоих случаях, обратите внимание, что регион «Запад», должен быть заключен в двойные кавычки, так как это текстовое значение.
Используете ли вы СУММЕСЛИ или СУММЕСЛИМН (которая может работать более чем по одному критерию) — это вопрос личных предпочтений. СУММЕСЛИМН была введена в Excel 2007, поэтому она будет теперь здесь в течение долгого времени.
Сумма, если ячейки не равны
=СУММЕСЛИ(диапазон;»<>значение»;ранг_суммы)
Для суммирования ячеек, когда другие клетки не равны определенному значению, можно использовать функцию СУММЕСЛИ.
В примере показано, ячейка Н5 содержит эту формулу:
=СУММЕСЛИ(C5:C14; «<>Западный»;E5:E14)
Эта формула суммирует суммы в колонке » Е » только тогда, когда регион в столбце C — не «Западный».
Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, включая «не равно», который вводится в качестве <>.
Когда вы используете оператор в критериях для такой функции, как СУММЕСЛИ, необходимо заключить его в двойные кавычки («»). В данном случае, критерий входа, как «<>Западный», который можно прочитать как «не равняется Западный», или просто «не Западный».
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН, для суммы, если ячейки не пустые. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
=СУММЕСЛИМН(объем; регион;»<>Западный»)
Обратите внимание, что диапазон суммы всегда стоит на первом месте в функции СУММЕСЛИМН.
СУММЕСЛИМН позволяет легко расширить критерии, обрабатывать более чем одно условие, если это необходимо.