17 авг. 2022 г.
читать 2 мин
Вы можете использовать следующие формулы в Excel для суммирования диапазона ячеек, если значение в соответствующем диапазоне не является пустым:
Формула 1: Сумма, если не пусто (один столбец)
=SUMIF( A:A , "<>", B:B )
Эта формула суммирует значения в столбце B только в том случае, если значения в столбце A не являются пустыми.
Формула 2: Сумма, если не пусто (несколько столбцов)
=SUMIFS( C:C , A:A , "<>", B:B , "<>")
Эта формула суммирует значения в столбце C только в том случае, если значения в столбцах A и B не являются пустыми.
В следующих примерах показано, как использовать каждую формулу на практике.
Пример 1: Сумма, если не пусто (один столбец)
На следующем снимке экрана показано, как суммировать значения в столбце « Очки », только если значения в столбце « Команда » не являются пустыми:
Сумма значений в столбце Очки для строк, где команда не пуста, равна 138 .
Мы можем убедиться, что это правильно, вручную рассчитав сумму очков для команд, которые не являются пустыми:
Сумма очков: 22 + 17 + 28 + 30 + 12 + 11 + 18 = 138 .
Это соответствует значению, которое мы рассчитали по формуле.
Пример 2. Сумма, если она не пуста (несколько столбцов)
На следующем снимке экрана показано, как суммировать значения в столбце « Очки », только если значения в столбцах « Конференция» и « Команда» не пусты:
Сумма значений в столбце « Очки » для строк, в которых столбцы « Конференция» и « Команда» заполнены, равна 90 .
Мы можем убедиться, что это правильно, вручную посчитав сумму баллов, где конференция и команда не являются пустыми:
Сумма очков: 22 + 17 + 28 + 12 + 11 = 90 .
Это соответствует значению, которое мы рассчитали по формуле.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как использовать СУММЕСЛИ с ИЛИ в Excel
Как суммировать, если ячейки содержат текст в Excel
Как использовать подстановочный знак в функции СУММЕСЛИМН в Excel
Подсчет непустых ячеек
Смотрите также4 используются только те
Перевложил файл -Не знаю даже активно пользуюсь. ДопустимSerge формула массива, так kim (не знаю,2) потому что
в Excel. те же самые, и критерий, то ячейки. Вставляем координаты, отличается от предыдущего ячейки, содержащие данныеФункция СЧЁТЗ используется для5 значения, которые входят лишние доллары в кто вы и
support.office.com
Подсчет заполненных ячеек в Microsoft Excel
=СУММЕСЛИ($F$9:$F$646;»N»;$M$9:$M$646). В данном: * воспользоваться. как является формулой почему формула массива так проверяется наличиеФункция что и у есть, условие, о диапазонов на листе,«Значение3» способа тем, что
– это воспользоваться подсчета только ячеек
Подсчет ячеек
6 в массив или формуле были как вас благодарить! случае формула находитМикки массива. Ее отличие первая) :-) кавычки — «{/post}{/quote}
Способ 1: счетчик на строке состояния
COUNTBLANK предыдущей – котором мы говорили в которых нужнои т.д. Когда позволяет зафиксировать подсчет информацией со счетчика, диапазона, содержащих данные.7 ссылку. Пустые ячейкиГость Это именно то критерий «N» в: О-ч-чень интересно а в том, чтоНасчет кавычки всеПардон, не сразу(СЧИТАТЬПУСТОТЫ) подсчитывает количество«Диапазон» выше. В поле подсчитать количество заполненных все данные введены. определенного диапазона в который размещен в
При подсчете ячеек иногда8 и текстовые значения: Здравствуйте, у меня о чем я диапазоне «$F$9:$F$646» а как буквы суммировать? благодаря особенности функции закономерно: понял что нужно пустых ячеек:и«Диапазон» ячеек числовыми данными. Жмем на кнопку отдельной ячейке. То правой части строки необходимо игнорировать пустыеA
Способ 2: функция СЧЁТЗ
в массиве или есть вопрос: как спрашивал! Спасибо затем суммируеет сосответсвующие И что дает СУММПРОИЗВ() она не=СЧЁТЕСЛИ(A1:A7;»») ищет текстовую считать НЕ пусто.=COUNTBLANK(A1:A7)«Условие»вводим координаты обрабатываемой Жмем кнопку
- «OK» есть, для просмотра состояния слева от ячейки. Предположим, нужноДанные ссылке игнорируются.
- можно посчитать количествоGuest числа в диапазоне в сумме Е+Щ требует специальной комбинации строку нулевой длинныПУСТО формулой вернуть=СЧИТАТЬПУСТОТЫ(A1:A7). Разница только в
- области, а в«OK». информации по нему кнопок переключения режимов определить общее количествоПродажиЕсли считать логические ячеек в диапазоне: А потом Вас «$M$9:$M$646″ ? клавишь.=СЧЁТЕСЛИ(A1:A7;»<>») ищет отсутствие невозможно, только текстовуюФункция том, что диапазонов поле.Данную функцию также можно область не нужно просмотра в Excel. продавцов, которые продали08.12.2008 значения, элементы текста по определенным критериям? устроил вариант сСуть вопроса вот
- SergeМой «провокационный» комментарий текстовой строки нулевой строку нулевой длинны.COUNTA
и соответствующих им - «Критерий»Для ручного введения формулы ввести вручную в будет постоянно выделять. Пока на листе единицу товара (столбец
Способ 3: функция СЧЁТ
19 и значения ошибок есть простая формула суммированием только значений, в чем: Можно: Миш, ему не имел целью напомнить длинны
- Тогда так:(СЧЕТЗ) подсчитывает количество условий может бытьвписываем условия. После придерживаемся следующего синтаксиса: ячейку или строкуВыделяем область, в которую выделен диапазон, в D).22,24 не требуется, используется СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования]). Где можно
- критерием для которых ли вместо критерия буквы суммировать надо. о том, что=СЧЁТЕСЛИ(A1:A7;»»») ищет кавычку1) СТРОКИ(A1:A7)-СЧЁТЕСЛИ(A1:A7;»<>») непустых ячеек: много. Вводим адреса этого жмем на=СЧЁТ(значение1;значение2;…) формул, придерживаясь следующего будет выводиться результат котором все элементыСЧЁТЗ игнорирует пустые значения
ИСТИНА функция СЧЁТ.
найти подобную функцию, - являются НЕПУСТЫЕ ячейки.{/post}{/quote} «N» написать формулуФормула СУММЕСЛИ, доп на первом месте=СЧЁТЕСЛИ(A1:A7;»<>»») ищет отсутствие
Способ 4: функция СЧЁТЕСЛИ
lapink2000=COUNTA(A1:A7) диапазонов и соответствующие кнопкуПосле этого в области, синтаксиса: подсчета. Кликаем по пустые или лишь в D3, D4,#ДЕЛ/0!Пример но только чтобыPirst, дружище наверное чтобы возвращала значение столбец… стоял, хоть и
- кавычки: *СТРОКИ() читать как=СЧЕТЗ(A1:A7) им условия, а«OK» в которой находится=СЧЁТЗ(значение1;значение2;…) значку один содержит какое-то
- D8 и D11Формула Описание (результат)Чтобы этот пример вместо суммирования значений я не смог НЕ ПУСТО?Sameer короткий, но намного=СЧЁТЕСЛИ(A1:A7;»»»»»») ищет две ЧСТРОК()Урок подготовлен для Вас потом жмем на. формула, отобразится количествоПосле того, как формула«Вставить функцию» значение, данный индикатор и подсчитывает только
=СЧЁТЗ(A2:A8) Подсчитывает количество проще было понять,
ячеек подсчитывалось их - свою мысль донести.Sameer: Микки, я имею более медленный вариант. кавычки подрядopenid.mail.ru/mail/vlad3622340 командой сайта office-guru.ru кнопку
Способ 5: функция СЧЁТЕСЛИМН
Для ручного ввода шаблон ячеек, заполненных числовыми введена, программа в. скрыт. Счетчик автоматически ячейки, содержащие значения непустых ячеек в скопируйте его на
- количество (непустых)? Заранее, Виноват. Русский ведь: Да я имел в виду чтоЮлия=СЧЁТЕСЛИ(A1:A7;»<>»»»»») ищет отсутствие: Спасибо, Кирилл! Вы,Источник: http://www.excel-easy.com/examples/count-blank-nonblank-cells.html«OK» выглядит следующим образом:
- данными. заранее выделенной областиОткрывается окно Мастера функций. появляется тогда, когда в столбце D. приведенном выше списке пустой лист. спасибо. не мой родной в виду это. он находит «Е»: Формула =СУММПРОИЗВ(—(A1:A100<>0))просто супер.Спасибо!!! двух кавычек подряд как всегда, знаетеПеревел: Антон Андронов.=СЧЁТЕСЛИ(диапазон;критерий)Данная функция позволяет подсчитать показывает результат подсчета
Ищем в представленном выделено две или
Функция найдет шесть - (6)Копирование примераPelena язык. Обьясняю как Сейчас попробую! Благодарю! или «Щ», а
Sameerkim все нюансы Excel.Автор: Антон АндроновСинтаксис у этой функцииПосле этого программа производит не просто количество заполненных ячеек указанного списке элемент более не пустых ячеек в столбце=СЧЕТЗ(A5:A8) Подсчитывает количествоСоздайте пустую книгу: могу. Спасибо еще
Dophin
lumpics.ru
Подсчёт пустых и непустых ячеек в Excel
затем суммирует заданный: Добрый день всем!: >По вопросу 2
openid.mail.ru/mail/vlad3622340 следующий: подсчет заполненных ячеек ячеек, заполненных числовыми
диапазона.
«СЧЁТЗ»
ячеек, и сразу D, содержащих значения, непустых ячеек в или лист.
200?'200px':''+(this.scrollHeight+5)+'px');">=СЧЁТЕСЛИ(диапазон;критерий)
раз!
: а СЧЁТЗ() не диапазон чисел находящийся
Ситуация такая: У
Честно скажу, не
понял, если есть
office-guru.ru
Количество непустых ячеек
: Добрый вечер!=СЧЁТЕСЛИМН(диапазон_условия1;условие1; диапазон_условия2;условие2;…)
выделенного диапазона, которые выражениями, но толькоКроме того, для подсчета. После того, как
показывает их число и отобразит «6» последних 4 строкахВыделите пример в
В качестве критерияJuokas катит разве?
в одной строке
меня таблица с задавался целью расставлять одна кавычка «Столкнулся с такой
После этого приложение производит соответствуют заданному условию,
такие из них, заполненных ячеек в это наименование выделено,
после слова в виде выходных
приведенного выше списка
разделе справки. поставить
: вот собственно иSerge с этит буквами
определенными данными. Слева приоритеты, думаю Vlad , она учитывается,
проблемой — как подсчет заполненных ячеек и выводит их
которые соответствуют определенному
Экселе существует ещё
жмем на кнопку«Количество» данных.
(4)Примечание. Не выделяйте»<>» проблема
: Не, определённые нужныSerge от таблицы я достаточно продвинутый, чтобы а если их подсчитать количество ячеек, указанных диапазонов, которые
в область указанную условию. Например, если функция счет. В
«OK».При выполнении определенных задач=СЧЁТЗ(A1:A7;2) Подсчитывает количество
заголовок строки или
Safe modealx74
:-): Я уже ответил. создал дополнительный столбец сделать это самостоятельно больше, то -
где не ПУСТО? соответствуют установленным условиям.
в первом пункте задать условие «>50», отличие от предыдущей.Но, хотя по умолчанию во время работы непустых ячеек и
столбца.: Подсчитывает количество непустых
: Функция СЧЁТЗ считает98765
Получается? где в определенных :-)
нет (странно).
СЧЁТЗ не подходит, Результат выводится в
этого способа. то будут учитываться
формулы, она считаетЗапускается окно аргументов. Аргументами
этот счетчик включен, с таблицей бывает
ячеек, содержащих значениеВыделение примера в значений в списке количество непустых ячеекGuestМикки
ячейках внес буквы.openid.mail.ru/mail/vlad3622340Ваша ф-ула СТРОКА(A1:A7)-СЧЁТЗ(A1:A7) т.к. учитывает и
заранее отмеченную область.Оператор СЧЁТЕСЛИМН является продвинутым только такие ячейки, только ячейки заполненные этой функции являются и только ждет
необходимость произвести подсчет 2, в приведенном
справке аргументов. Функция СЧЁТЗ в диапазоне. Что: Видимо такая формула
: Микки, я имею Чтобы суммировать по: =СУММПРОИЗВ(—(A1:A7<>»»)) работает и возвращает 1-СЧЁТЗ(A1:A7), получается
те ячейки, гдеКак видим, простейший подсчет вариантом функции СЧЁТЕСЛИ. в которых содержится числовыми данными. ссылки на ячейки. того, когда пользователь заполненных данными ячеек. выше списке (7)
Нажмите сочетание клавиш используется для подсчета должно быть посчитано нужна: в виду что какому-либо критерию я не как формула
отрицательное число(может ошиблись?) ПУСТО возвращено формулой.
planetaexcel.ru
Как подсчитать непустые ячейки?
количества заполненных ячеек Его используют, когда
значение больше числаКак и в предыдущем Ссылку на диапазон выделит определенные элементы, Эксель предоставляет такую=СЧЁТЗ(A1:A7;»два») Подсчитывает количество CTRL+C. количества ячеек с у Вас не=СУММЕСЛИ($F$9:$F$646;»<>»&»»;$M$9:$M$646) он находит «Е» обычно пользуюсь СУММЕСЛИ. массива.openid.mail.ru/mail/vlad3622340А вот для
выделенного диапазона можно нужно указать более 50. Также можно случае, выделяем ячейку,
можно прописать вручную, в некоторых случаях
возможность с помощью непустых ячеек тНа листе выделите данными в интервале понятно. Если ячейкиВалерий
или «Щ», а Но что делать
Кирилл, благодарю за: Извиняюсь, не видел количества ячеек с увидеть в строке одного условия соответствия задавать значения «»
куда будут выводиться но лучше установить он может быть
встроенных инструментов. Давайте ячеек, содержащих значение
ячейку A1 и или массиве. по столбцу C,: Может, я чего-то затем суммирует заданный если я хочу столь подробное объяснение! последние сообщения, ЧСТРОК() видимой информацией сляпал
состояния Эксель. Если для различных диапазонов. (не равно) и
данные и таким курсор в поле отключен вручную. Тогда выясним, как выполнить «два» в приведенном нажмите сочетание клавишСинтаксис то уберите объединенные не понял, но
диапазон чисел находящийся чтобы он суммировал Как всегда, всё конечно же работает.=СЧЁТЕСЛИ(A1:A7;»<>»»»)-СЧЁТЕСЛИ(A1:A7;»») же вам нужно Всего можно указать
т.д. же способом запускаем«Значение1» актуальным становится вопрос указанную процедуру в выше списке (7) CTRL+V.СЧЁТЗ(значение1; значение2;…) ячейки и в ответ мне кажется в одной строке дипазоны по непустым от Вас сохраняю
Спасибо.Ещё разъясните пожалуйста, вывести результат в до 126 условий.После того, как выделили Мастер функций. В
, куда нужно ввести о его включении. этой программе.Олег якубов
Чтобы переключиться междуЗначение1, значение2,..— от путь.
очевидным: с этит буквами{/post}{/quote} ячейкам?
в своей копилке
openid.mail.ru/mail/vlad3622340 почему =СЧЁТЕСЛИ(A1:A7;»<>»»») считает отдельную область на
Обозначаем ячейку, в которую
ячейку для вывода нём выбираем оператора данные, и выделить Для этого нужноСкачать последнюю версию
: Кликни правой кнопкой просмотром результатов и
1 до 255Juokas=сумм(диапазон) — суммесли(диапазон
Если бы вы
Serge по Excel.
: Спасибо, Kim! любые все ячейки листе, а тем
будет выводиться результат
результата и запустили с наименованием соответствующую область на кликнуть правой кнопкой Excel мыши на нижней
просмотром формул, возвращающих аргументов, представляющих значения,: Спасибо! поиска;»»;диапазон суммирования) прочитали правила и: Так он (?)
Владимир»Век живи и заданного диапазона. более произвести подсчет и запускаем Мастер Мастер функций, выбираем«СЧЁТ» листе. Если нужно
planetaexcel.ru
как посчитать количество непустых ячеек (Формулы/Formulas)
мыши по строкеВ программе Excel количество планке диалогового окна
эти результаты, нажмите которые требуется сосчитать._Boroda_Валерий выложили пример того и так суммирует: =СУММ(ЕСЛИ($A$1:$A$7=»»;1)) — массив век учись», иlapink2000 с учетом определенных функций. В нем
запись. Жмем на кнопку
произвести подсчет заполненных состояния и в заполненных ячеек можно Екселя. сочетание клавиш CTRL+`
Замечания: И для каждой: Видимо такая формула
что Вы хотите
непустые...
Vlad, но у в голову не: 1) СТРОКИ(A1:A7)-СЧЁТЗ(A1:A7)
excelworld.ru
Подсчет количества непустых ячеек (Формулы)
условий, то в ищем элемент«СЧЁТЕСЛИ»«OK» ячеек в нескольких открывшемся списке установить увидеть при помощиBrigelo (тупое ударение) илиЗначением является любой ячейки с подсчетом нужна: .. Вы быВ пустых-то что
Вас там объединенные пришло использовать ЧСТРОК2) потому что
этом случае придут«СЧЁТЕСЛИМН». Кликаем по кнопке
excelworld.ru
Как посчитать количество не пустых ячеек в заданной области в Екселе?
. удаленных друг от галочку напротив пункта счетчика на строке: Выделить диапазон и на вкладке Формулы тип информации, включая переписывать диапазон? Неее,
=СУММЕСЛИ($F$9:$F$646;»<>»&»»;$M$9:$M$646){/post}{/quote}
уже получили десяток
суммировать? ячейки. Вы вроде :-) так проверяется наличие
на помощь специализированные
. Выделяем его и«OK»Запускается окно аргументов. Аргументы друга диапазонах, то«Количество» состояния или целого
в нижней планке в группе Зависимости значения ошибок и лениво.Ну да, так решений …Sameer не лузер…lapink2000
кавычки — « функции. жмем на кнопку. те же самые,
координаты второго, третьего
. После этого счетчик ряда функций, каждая окна там где формул нажмите кнопку
отсутствие текста («»),
Кстати, а причем даже лучше.
Sameer: Я имею формулу
lapink2000: Vlad,kim
Автор: Максим Тютюшев«OK»
Открывается окно аргументов. У что и при
и последующего диапазона опять будет отображаться. из которых подсчитывает высвечивается сумма, кликнуться
Отобразить формулы. но не пустые тут объединенные ячейки?Guest: Спасибо Сергей, как вроде: «Суммировать заданный: Извиняюсь за «буквоедство»,Я еще раз: =СУММПРОИЗВ(—(A1:A7<>»»))Этот пример продемонстрирует, как
.
данной функции два
использовании предыдущего способа.
нужно вводить в
Подсчитать количество заполненных ячеек
элементы, заполненные определенным
правой кнопкой мыши
1
ячейки.
Они разве мешают?
: Видимо такая формула
я понял вы
диапозон если ячейка
но это крайне
вчитался: наверное все
=ЧСТРОК(A1:A7)-СЧЁТЕСЛИ(A1:A7;»»)
сосчитать количество пустых
Происходит открытие окна аргументов. аргумента: диапазон, где В их роли поля под названием
можно при помощи типом данных. и выбрать кол-во2Если аргумент является
Предлагаю так нужна: имели в виду содержит хоть какую-то важно понимать:
же вам нужнаlapink2000 и непустых ячеек Собственно, аргументы функции происходит подсчет ячеек,
выступают ссылки на«Значение2» функции СЧЁТЗ. ОнаСамый простой способ подсчитать значений.
3 массивом или ссылкой,200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЗ(C4:ИНДЕКС(C1:C999;ЕСЛИОШИБКА(ПОИСКПОЗ(A2+1;A1:A999;)-1;999)))=СУММЕСЛИ($F$9:$F$646;»<>»&»»;$M$9:$M$646){/post}{/quote} СУММЕСЛИ. Я им букву или число»=СУММПРОИЗВ(—(A1:A7<>»»)) работает как вторая формула от
: 1) СТРОКИ(A1:A7)-СЧЁТЗ(A1:A7)
Skip to content
В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Хорошо знакомая нам функция ЕСЛИ позволяет производить вычисления в зависимости от выполнения условия. Функция СУММ позволяет складывать числовые значения. А что если нам нужна формула ЕСЛИ СУММ? Для этого случая в Excel имеется специальная функция СУММЕСЛИ.
Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif в английской версии) в таблицах Excel. Начнем с самых простых случаев, как можно использовать при этом знаки подстановки, назначить диапазон суммирования, работать с числами, текстом и датами. Особо остановимся на том, как использовать сразу несколько условий. И, конечно, мы применим новые знания на практике, рассмотрев несложные примеры.
- Как пользоваться СУММЕСЛИ в Excel – синтаксис
- Примеры использования функции СУММЕСЛИ в Excel
- Сумма если больше чем, меньше, или равно
- Критерии для текста.
- Подстановочные знаки для частичного совпадения.
- Точная дата либо диапазон дат.
- Сумма значений, соответствующих пустым либо непустым ячейкам
- Сумма по нескольким условиям.
- Почему СУММЕСЛИ у меня не работает?
Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.
Как пользоваться СУММЕСЛИ в Excel – синтаксис
Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.
Синтаксис функции выглядит следующим образом:
=СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования])
Диапазон – это область, которую мы исследуем на соответствие определённому значению.
Критерий – это значение или шаблон, по которому мы производим отбор чисел для суммирования.
Значение критерия может быть записано прямо в самой формуле. В этом случае не забывайте, что текст нужно обязательно заключать в двойные кавычки.
Также он может быть представлен в виде ссылки на ячейку таблицы, в которой будет указано требуемое ограничение. Безусловно, второй способ является более рациональным, поскольку позволяет гибко менять расчеты, не редактируя выражение.
Диапазон_суммирования — третий параметр, который является необязательным, однако он весьма полезен. Благодаря ему мы можем производить поиск в одной области, а суммировать значения из другой в соответствующих строках.
Итак, если он указан, то расчет идет именно по его данным. Если отсутствует, то складываются значения из той же области, где производился поиск.
Чтобы лучше понять это описание, рассмотрим несколько простых задач. Надеюсь, что они будут понятны не только «продвинутым» пользователям, но и подойдут для «чайников».
Примеры использования функции СУММЕСЛИ в Excel
Сумма если больше чем, меньше, или равно
Начнем с самого простого. Предположим, у нас есть данные о продажах шоколада. Рассчитаем различные варианты продаж.
В I3 записано:
=СУММЕСЛИ(D2:D21;I2)
D2:D21 – это координаты, в которых мы ищем значение.
I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.
Поскольку третий параметр функции не указан, то мы сразу складываем отобранные числа. Область поиска будет одновременно являться и диапазоном суммирования.
Кроме того, в качестве задания для отбора нужных значений можно указать текстовое выражение, состоящее из знаков >, <, <>, <= или >= и числа.
Можно указать его прямо в формуле, как это сделано в I13
=СУММЕСЛИ(D2:D21;«<144»)
То есть подытоживаем все заказы, в которых количество меньше 144.
Но, согласитесь, это не слишком удобно, поскольку нужно корректировать саму формулу, да и условие еще нужно не забыть заключить в кавычки.
В дальнейшем мы будем стараться использовать только ссылку на критерий, поскольку это значительно упрощает возможные корректировки.
Критерии для текста.
Гораздо чаще встречаются ситуации, когда поиск нужно проводить в одном месте, а в другом — суммировать данные, соответствующие найденному.
Чаще всего это необходимо, если необходимо использовать отбор по определённым словам. Ведь текстовые значения складывать нельзя, а вот соответствующие им числа – можно.
Как простой прием использования формулы СУММ ЕСЛИ в Эксель таблицах, рассчитаем итог по выполненным заказам.
В I3 запишем выражение:
=СУММЕСЛИ(F2:F21;I2;E2:Е21)
F2:F21 – это область, в которой мы отбираем подходящие значения.
I2 – здесь записано, что именно отбираем.
E2:E21 – складываем числа, соответствующие найденным совпадениям.
Конечно, можно указать параметр отбора прямо в выражении:
=СУММЕСЛИ(F2:F21;”Да”;E2:Е21)
Но мы уже договорились, что так делать не совсем рационально.
Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.
Подстановочные знаки для частичного совпадения.
При работе с текстовыми данными часто приходится производить поиск по какой-то части слова или фразы.
Вернемся к нашему случаю. Определим, сколько всего было заказов на черный шоколад. В результате, у нас есть 2 подходящих наименования товара. Как учесть их оба? Для этого есть понятие неточного соответствия.
Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.
Символ “?” позволяет заменить собой один любой символ.
Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).
Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –
=СУММЕСЛИ(C2:C21;I2;E2:Е21) , где в E2 записано *[слово]*
либо
=СУММЕСЛИ(C2:C21;»*»&I2&»*»;E2:E21)
где * вставлены прямо в выражение и «склеены» с нужным текстом.
Давайте потренируемся:
- “*черный*” — мы ищем фразу, в которой встречается это выражение, а до него и после него – любые буквы, знаки и числа. В нашем случае этому соответствуют “Черный шоколад” и “Супер Черный шоколад”.
- “Д?” — необходимо слово из 2 букв, первая из которых “Д”, а вторая – любая. В нашем случае подойдет “Да”.
- “???” — найдем слово из любых 3 букв
=СУММЕСЛИ(F2:F21;”???”;E2:E21)
Этому требованию соответствует “Нет”.
- “???????*” — текст из любых 7 и более букв.
=СУММЕСЛИ(B2:B21;“???????*”;E8:E28)
Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.
- “З*” — мы выбираем фразу, первая буква которой “З”, а далее – любые буквы, знаки и числа. Это “Золотой” и “Зеленый”.
- “Черный*” — подходит фраза, которая начинаются именно с этого слова, а далее – любые буквы, знаки и числа. Подходит “Черный шоколад”.
Примечание. Если вам необходимо в качестве задания для поиска применять текст, который содержит в себе * и ?, то используйте знак тильда (~), поставив его перед этими символами. Тогда * и ? будут считаться обычными символами, а не шаблоном:
=СУММЕСЛИ(B2:B21;“*~?*”;E8:E28)
Важное замечание. Если в вашем тексте для поиска встречается несколько знаков * и ?, то тильду (~) нужно поставить перед каждым из них. К примеру, если мы будем искать текст, состоящий из трех звездочек, то формулу ЕСЛИ СУММ можно записать так:
=СУММЕСЛИ(B2:B21;“~*~*~*”;E8:E28)
А если текст просто содержит в себе 3 звездочки, то можно наше выражение переписать так:
=СУММЕСЛИ(B2:B21;“*~*~*~**”;E8:E28)
Точная дата либо диапазон дат.
Если нам нужно найти сумму чисел, соответствующих определённой дате, то проще всего в качестве критерия указать саму эту дату.
Примечание. При этом не забывайте, что формат указанной вами даты должен соответствовать региональным настройкам вашей таблицы!
Обратите внимание, что мы также можем здесь вписать ее прямо в формулу, а можем использовать ссылку.
Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.
=СУММЕСЛИ(A2:A21;I1;E2:E21)
или же
=СУММЕСЛИ(A2:A21;СЕГОДНЯ();E2:E21)
Рассчитаем за вчерашний день.
=СУММЕСЛИ(A2:A21;СЕГОДНЯ()-1;E2:E21)
СЕГОДНЯ()-1 как раз и будет «вчера».
Складываем за даты, которые предшествовали 1 февраля.
=СУММЕСЛИ(A2:A21;»<«&»01.02.2020»;E2:E21)
После 1 февраля включительно:
=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21)
А если нас интересует временной интервал «от-до»?
Мы можем рассчитать итоги за определённый период времени. Для этого применим маленькую хитрость: разность функций СУММЕСЛИ. Предположим, нам нужна выручка с 1 по 4 февраля включительно. Из продаж после 1 февраля вычитаем все, что реализовано после 4 февраля.
=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21) — СУММЕСЛИ(A2:A21;»>=»&»04.02.2020″;E2:E21)
Сумма значений, соответствующих пустым либо непустым ячейкам
Случается, что в качестве условия суммирования нужно использовать все непустые клетки, в которых есть хотя бы одна буква, цифра или символ.
Рассмотрим ещё один вариант использования формулы СУММ ЕСЛИ в таблице Excel, где нам необходимо подсчитать заказы, в которых нет отметки о выполнении, а также сколько было вообще обработанных заказов.
Если критерий указать просто “*”, то мы учитываем для подсчета непустые ячейки, в которых имеется хотя бы одна буква или символ (кроме пустых).
=СУММЕСЛИ(F2:F21;»*»;E2:E21)
Точно такой же результат даёт использование вместо звездочки пары знаков «больше» и «меньше» — <>.
=СУММЕСЛИ(F2:F21;»<>»;E2:E21)
Теперь рассмотрим, как можно находить сумму, соответствующую пустым ячейкам.
Для того, чтобы найти пустые, в которых нет ни букв, ни цифр, в качестве критерия поставьте парные одинарные кавычки ‘’, если значение критерия указано в ячейке, а формула ссылается на неё.
Если же указать на отбор только пустых ячеек в самой формуле СУММ ЕСЛИ, то впишите двойные кавычки.
=СУММЕСЛИ(F2:F21;«»;E2:E21)
Сумма по нескольким условиям.
Функция СУММЕСЛИ может работать только с одним условием, как мы это делали ранее. Но очень часто случается, что нужно найти совокупность данных, удовлетворяющих сразу нескольким требованиям. Сделать это можно как при помощи некоторых хитростей, так и с использованием других функций. Рассмотрим все по порядку.
Вновь вернемся к нашему случаю с заказами. Рассмотрим два условия и посчитаем, сколько всего сделано заказов черного и молочного шоколада.
1. СУММЕСЛИ + СУММЕСЛИ
Все просто:
=СУММЕСЛИ($C$2:$C$21;»*»&H3&»*»;$E$2:$E$21)+СУММЕСЛИ($C$2:$C$21;»*»&H4&»*»;$E$2:$E$21)
Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).
Это самое простое решение, но не самое универсальное и далеко не единственное.
2. СУММ и СУММЕСЛИ с аргументами массива.
Вышеупомянутое решение очень простое и может выполнить работу быстро, когда критериев немного. Но если вы захотите работать с несколькими, то она станет просто огромной. В этом случае лучшим подходом является использование в качестве аргумента массива критериев. Давайте рассмотрим этот подход.
Вы можете начать с перечисления всех ваших условий, разделенных запятыми, а затем заключить итоговый список, разделенный точкой с запятой, в {фигурные скобки}, который технически называется массивом.
Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:
СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21)
Поскольку здесь использован массив критериев, то результатом вычислений также будет массив, состоящий из двух значений.
А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.
=СУММ(СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21))
Важно, что результаты вычислений в первом и втором случае совпадают.
3. СУММПРОИЗВ и СУММЕСЛИ.
А если вы предпочитаете перечислять критерии в какой-то специально отведенной для этого части таблицы? Можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в заданных массивах и возвращает сумму этих произведений.
Вот как это будет выглядеть:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;H3:H4;E2:E21))
в H3 и H4 мы запишем критерии отбора.
Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;{«*черный*»;»*молочный*»};E2:E21))
Результат, возвращаемый в обоих случаях, будет идентичен тому, что вы наблюдаете на скриншоте.
Важное замечание! Обратите внимание, что все перечисленные выше три способа производят расчет по логическому ИЛИ. То есть, нам нужны продажи шоколада, который будет или черным, или молочным.
Почему СУММЕСЛИ у меня не работает?
Этому может быть несколько причин. Иногда ваше выражение не возвращает того, что вы ожидаете, только потому, что тип данных в ячейке или в каком-либо аргументе не подходит для нее. Итак, вот что нужно проверить.
1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.
Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив {1,2,3}, Excel выдаст сообщение об ошибке.
Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)
Неверно : =СУММЕСЛИ({1,2,3}, «цвет», C1:C3)
2. Ошибка при суммировании значений из других листов или рабочих книг.
Как и любая другая функция Excel, СУММЕСЛИ может ссылаться на другие листы и рабочие книги, если они в данный момент открыты.
Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:
=СУММЕСЛИ([Книга1.xlsx]Лист1!$A$2:$A$9,»яблоки»,[Книга1.xlsx]Лист1!$F$2:$F$9)
Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.
3. Чтобы избежать проблем, убедитесь, что диапазоны данных и поиска имеют одинаковый размер.
Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.
Примеры расчета суммы:
Если у вас есть список данных, который содержит как значения, так и пустые ячейки, теперь вы хотите суммировать значения в другом столбце на основе критериев, определяющих, имеет ли соответствующая ячейка в вашем списке значения или нет. Вообще говоря, обычная функция СУММЕСЛИ может решить эту проблему быстро и легко.
- Сумма значений на основе пустых ячеек в Excel
- Сумма значений на основе непустых ячеек в Excel
Сумма значений на основе пустых ячеек в Excel
Чтобы суммировать значения на основе пустых ячеек, примените функцию СУММЕСЛИ, общий синтаксис:
=SUMIF(range, “”, sum_range)
- range: Диапазон ячеек, содержащих пустые ячейки;
- “”: Двойные кавычки представляют собой пустую ячейку;
- sum_range: Диапазон ячеек, по которым вы хотите произвести суммирование.
Возьмите приведенный выше снимок экрана в качестве примера, чтобы суммировать общую сумму, в которой продукты являются пустыми ячейками в поле Product. Пожалуйста, сделайте так:
Скопируйте или введите приведенную ниже формулу в пустую ячейку и нажмите Enter ключ для получения результата:
=SUMIF(B2:B12,»»,C2:C12)
Сумма значений на основе непустых ячеек в Excel
Функция СУММЕСЛИ также может помочь суммировать значения на основе непустых ячеек, общий синтаксис:
=SUMIF(range,»<>»,sum_range)
- range: Диапазон ячеек, содержащих пустые ячейки;
- “<>”: Критерии, указывающие на непустые ячейки;
- sum_range: Диапазон ячеек, по которым вы хотите произвести суммирование.
Чтобы суммировать значения, в которых соответствующие ячейки не пустые, скопируйте или введите формулу ниже, а затем нажмите Enter ключ для возврата нужного вам результата:
=SUMIF(B2:B12,»<>»,C2:C12)
Используемая относительная функция:
- SUMIF:
- Функция СУММЕСЛИ может помочь суммировать ячейки на основе одного критерия.
Другие статьи:
- Сумма, если ячейки содержат определенный текст в другом столбце
- Из этого туториала Вы узнаете, как суммировать значения, если ячейки содержат определенный или частичный текст в другом столбце. В качестве примера возьмем диапазон данных ниже, чтобы получить общее количество продуктов, содержащих текст «Футболка», с этой задачей в Excel могут справиться как функция СУММЕСЛИ, так и функция СУММПРОИЗВ.
- Сумма, если больше или меньше определенного значения
- Чтобы суммировать список значений, которые больше или меньше определенного значения, обычная функция СУММЕСЛИ может помочь вам справиться с этой задачей. В этой статье я расскажу о том, как использовать функцию СУММЕСЛИ, чтобы решить эту проблему в Excel.
- Сумма, равная одному из многих
- Нам может быть легко суммировать значения на основе заданных критериев с помощью функции СУММЕСЛИ. Но иногда вам может потребоваться суммировать значения на основе списка элементов. Например, у меня есть диапазон данных, продукты которого перечислены в столбце A, а соответствующие суммы продаж указаны в столбце B. Теперь я хочу получить общую сумму на основе перечисленных продуктов в диапазоне D4: D6, как показано ниже. . Как быстро и легко решить эту проблему в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel — Помогает вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.
- Разработан для 1500 рабочих сценариев, помогает решить 80% проблем с Excel.
- Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
- Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
- 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Вкладка Office — включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Комментарии (0)
Оценок пока нет. Оцените первым!
You can use the following methods to sum non-contiguous cells in Excel:
Method 1: Sum Non-Contiguous Cells
=SUM(A2, A6, A11)
This particular formula will calculate the sum of the values in cells A2, A6, and A11.
Method 2: Sum Non-Contiguous Cells and Contiguous Cells
=SUM(A2:A7, A10, A12)
This particular formula calculates the sum of the values in the range A2 to A7 along with the values in cell A10 and A12.
The following examples show how to use each formula in practice with the following dataset in Excel:
We can type the following formula into cell C2 to calculate the sum of the values in cells A2, A6, and A11:
=SUM(A2, A6, A11)
Once we press Enter, the sum of the values in these cells will be shown:
The sum of this non-contiguous set of cells is 23.
Example 2: Sum Non-Contiguous Cells and Contiguous Cells
We can type the following formula into cell C2 to sum the values of some non-contiguous cells and some contiguous cells:
=SUM(A2:A7, A10, A12)
Once we press Enter, the sum of the values in these cells will be shown:
The sum of these cells is 71.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Sum Cells with Text and Numbers
Excel: How to Sum If Not Blank
Excel: How to Use SUMIF with OR