Ячейка начинается с определенного значения
=СУММЕСЛИ(диапазон;"текст*";ранг-сумм)
=SUMIF(диапазон;"текст*";ранг-сумм)
=СУММЕСЛИ(C5:C11; "футболка*"; D5:D11)
=SUMIF(C5:C11; "футболка*"; D5:D11)
Эта формула суммирует суммы в столбце D, когда значение в столбце C начинается с "футболка". Обратите внимание, что СУММЕСЛИ нечувствительна к регистру.
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает "один или более символов", а вопросительный знак (?) означает "любой один символ".
Эти шаблоны позволяют создавать такие критерии, как "начинается с", "кончается на", "содержит 3 символа" и так далее.
Чтобы соответствовали все предметы, которые начинаются на "футболка", нужны критерии "футболка*". Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки ("").
Вы также можете использовать функцию СУММЕСЛИМН для суммирования, если ячейки начинают с. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
=СУММЕСЛИМН(Д5:Д11;С5:С11;"футболка*")
=SUMIFS(Д5:Д11;С5:С11;"футболка*")
Сумма значений между определенными числами
=СУММЕСЛИМН(ранг-сумм;ранг_критериев;">500";ранг_критериев;"<1000")
=SUMIFS(ранг-сумм;ранг_критериев;">500";ранг_критериев;"<1000")
В сумме, если между можно использовать СУММЕСЛИМН по двум критериям.
В показанном примере, ячейка F7 содержит эту формулу:
=СУММЕСЛИМН(D5:D11;D5:D11; ">500";D5:D11;"<1000")
=SUMIFS(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")
=SUMIFS(сумма;сумма;">=500";количество;"<=1000")
=СУММЕСЛИМН(сумма;сумма;">="&А1;сумма;"<"&В1)
=SUMIFS(сумма;сумма;">="&А1;сумма;"<"&В1)
где A1-это ссылка на нижний порог и B1-это ссылка на верхний порог
Сумма, если за год
=СУММЕСЛИМН(ранг-суммы;Дата;">="&Дата(Год);Дата;"<="&Дата(год;12;31))
=SUMIFS(ранг-суммы;Дата;">="&DATE(Год);Дата;"<="&DATE(год;12;31))
Если Вам необходимо суммировать, если за год, можно использовать функцию СУММЕСЛИМН по двум критериям.
В показанном примере, формула в Н6 является:
=СУММЕСЛИМН(сумма;Дата;">="&Дата(G6;1;1);даты;"<="&Дата(G6;12;31))
=SUMIFS(сумма;Дата;">="&DATE(G6;1;1);даты;"<="&DATE(G6;12;31))
В результате получается общая сумма за 2011 год. При копировании вниз формулы создается общая сумма для 2012 и 2013 годов.
Первый аргумент для функции СУММЕСЛИМН всегда суммируемый диапазон ("ранг_суммы"), и критерии поставляются в виде одного или нескольких диапазона/пары критериев.
Диапазон суммы в этом примере - это именованный диапазон, называемый "сумма" (Е3:E2931), и критерии поставляются в виде двух пар, как с помощью именованного диапазона под названием "Дата" (Б3:B2931).
Каждый раз функция ДАТА используется в критериях для построения двух допустимых дат, как с помощью того же года:
1. В первый день 2011 года
2. В последний день 2011 года
Дата; ">="&Дата(G6;1;1) // Дата >= к 1/1/2011
Дата; "<="&Дата(G6;12;31) // Дата <= 12/31/2011
В результате, формула рассчитывает общую сумму всех сумм только в 2011 году.
Поскольку мы используем ссылку на ячейку, чтобы поставить год, формула может быть скопирована вниз, чтобы также создать итог на 2012 и 2013 годы.
Сумма, если ячейка содержит текст в другой ячейке
=СУММЕСЛИ(диапазон;"*"&А1&"*";ранг_суммы)
=SUMIF(диапазон;"*"&А1&"*";ранг_суммы)
Чтобы суммировать ячейки,которые содержат определенный текст в другой ячейке, вы можете использовать функцию СУММЕСЛИ с подстановкой и конъюнкцией.
В примере показано, ячейка G7 содержит эту формулу:
=СУММЕСЛИ(C5:C11; "*"&F7&"*";D5:D11)
=SUMIF(C5:C11; "*"&F7&"*";D5:D11)
Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит текст из ячейки F7.
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает "один или более символов", а вопросительный знак (?) означает "любой один символ".
Эти шаблоны позволяют создавать такие критерии, как "начинается с", "кончается на", "содержит 3 символов" и так далее.
Так, например, можно использовать такие критерии, как "*шляпа*" для поиска текста в любом месте ячейки.
Когда Excel вычисляет этот аргумент внутри функции СУММЕСЛИ, он будет "видеть" это: "*кофта*".
Обратите внимание, что СУММЕСЛИ нечувствительна к регистру.
Альтернатива с СУММЕСЛИМН
=СУММЕСЛИМН(D5:D11;С5:С11;"*кофта*")
=SUMIFS(D5:D11;С5:С11;"*кофта*")
Обратите внимание, что диапазон суммы всегда стоит на первом месте в функции СУММЕСЛИМН.
Сумма, если ячейки равны
Если вам нужно сложить числа на основе других ячеек равных определенному значению, вы можете легко это сделать с помощью функции СУММЕСЛИ или функции СУММЕСЛИМН.
В показанном примере, мы подсчитываем все продажи в Западном регионе. Формула в ячейке H4 является:
=СУММЕСЛИ(C5:C14; "Западный";E5:E14)
=SUMIF(C5:C14; "Западный";E5:E14)
Формула в ячейке Н5 является:
=СУММЕСЛИМН(E5:E14;C5:C14;"Западный")
=SUMIFS(E5:E14;C5:C14;"Западный")
Обе формулы относятся к названной области диапазонов (С5:C14) и сумм (Е5:Е14).
Обе формулы используют встроенные функции для расчета промежуточных итогов, но синтаксис функции СУММЕСЛИ и СУММЕСЛИМН немного отличается:
СУММЕСЛИ(диапазон;критерий;ранг_суммы)
SUMIF(диапазон;критерий;ранг_суммы)
СУММЕСЛИМН(ранг_сцммы;диапазон;критерий)
= SUMIFS(ранг_сцммы;диапазон;критерий)
В обоих случаях, обратите внимание, что регион "Запад", должен быть заключен в двойные кавычки, так как это текстовое значение.
Используете ли вы СУММЕСЛИ или СУММЕСЛИМН (которая может работать более чем по одному критерию) - это вопрос личных предпочтений. СУММЕСЛИМН была введена в Excel 2007, поэтому она будет теперь здесь в течение долгого времени.
Сумма, если ячейки не равны
=СУММЕСЛИ(диапазон;"<>значение";ранг_суммы)
=SUMIF(диапазон;"<>значение";ранг_суммы)
Для суммирования ячеек, когда другие клетки не равны определенному значению, можно использовать функцию СУММЕСЛИ.
=СУММЕСЛИ(C5:C14; "<>Западный";E5:E14)
=SUMIF(C5:C14; "<>Западный";E5:E14)
Эта формула суммирует суммы в колонке " Е " только тогда, когда регион в столбце C - не "Западный".
Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, включая "не равно", который вводится в качестве <>.
Когда вы используете оператор в критериях для такой функции, как СУММЕСЛИ, необходимо заключить его в двойные кавычки (""). В данном случае, критерий входа, как "<>Западный", который можно прочитать как "не равняется Западный", или просто "не Западный".
Альтернатива с СУММЕСЛИМН
=СУММЕСЛИМН(объем; регион;"<>Западный")
=SUMIFS(объем; регион;"<>Западный")
Обратите внимание, что диапазон суммы всегда стоит на первом месте в функции СУММЕСЛИМН.
СУММЕСЛИМН позволяет легко расширить критерии, обрабатывать более чем одно условие, если это необходимо.
скачать файл
Автор проекта
Дмитрий Якушев
Привет! Меня зовут Дмитрий. С 2014 года Microsoft Cretified Trainer. Вместе с командой управляем этим сайтом. Наша цель - помочь вам эффективнее работать в Excel.
Изучайте наши статьи с примерами формул, сводных таблиц, условного форматирования, диаграмм и макросов. Записывайтесь на наши курсы или заказывайте обучение в корпоративном формате.
Сайт https://akademia-excel.ru/
ИП Якушев Дмитрий Александрович
ОГРНИП: 314501721200022
ИНН: 501706813807
Образовательная лицензия № Л035-01255-50/01194039
Почта: info@akademia-excel.ru
Телефон для связи: + 7 (958) 697-73-88, + 7 (495) 145-23-86
Регистрация на сайте означает согласие с пользовательским соглашением и на получение рассылки и рекламных материалов.
Политика в отношении обработки и защиты персональных данных.
Банковские реквизиты:
Расчетный счет 40802810500000003597
Банк АО «Тинькофф Банк»
Юридический адрес Банка Москва, 123060,1-й Волоколамский проезд, д. 10, стр. 1
Корр. счет Банка 30101810145250000974
ИНН Банка 7710140679
БИК Банка 044525974
Подписывайтесь на нас в соц.сетях:
Сайт https://akademia-excel.ru/
ИП Якушев Дмитрий Александрович
ОГРНИП: 314501721200022
ИНН: 501706813807
Образовательная лицензия № Л035-01255-50/01194039
Банковские реквизиты:
Расчетный счет 40802810500000003597
Банк АО «Тинькофф Банк»
Юридический адрес Банка Москва, 123060,1-й Волоколамский проезд, д. 10, стр. 1
Корр. счет Банка 30101810145250000974
ИНН Банка 7710140679
БИК Банка 044525974
Регистрация на сайте означает согласие с пользовательским соглашением и на получение рассылки и рекламных материалов.
Политика в отношении обработки и защиты персональных данных.
Почта: info@akademia-excel.ru
Телефон для связи: + 7 (958) 697-73-88, + 7 (495) 145-23-86
Написать в тех.поддержку
Подписывайтесь:
авторизуйтесь