Как в экселе поставить значок суммы: Как написать знак суммы (количества) в таблицах Excel.

Как написать знак суммы (количества) в таблицах Excel.

При написании формул в документах MS Excel или Word зачастую возникает потребность в написании математического знака суммы (он же «знак суммирования» или «знак нахождения суммы»). Некоторые далекие от математических исчислений люди называют его «знак количества».

Выглядит знак суммы, как угловатая растянутая относительно горизонтальной оси симметрии заглавная русская буква «Е» или английская «E».

Вставить знак суммы в ячейку таблицы «эксель» или на лист «вордовского» документа можно двумя методами, которые приведены ниже:

1. При помощи таблицы символов;

2. При помощи опций на панели быстрого доступа в самой программе.

Как поставить знак суммы при помощи таблицы символов.

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

• Войти в меню «Пуск» => Программы => Стандартные => Служебные => Таблица символов.
• Выбрать символ суммирования;
• Скопировать символ суммирования;
• Вставить символ в таблицу или документ «Ворда».

Способ нехитрый и не занимает много времени.

Как поставить знак суммы при помощи встроенных возможностей «Эксель».

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

• Выбрать ячейку, в которую поставите знак суммы;
• В меню быстрого доступа выбрать вкладку «Вставка»
• Выбрать пиктограмму «Символ»;
• В появившемся окне выбрать знак суммы и вставить его при помощи кнопки «Вставить»

Подробное пошаговое видео о том, как выполняются описанные выше действия:

Удачи в изучении программы Excel.

СУММ (функция СУММ) — Служба поддержки Office

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

Пример

• 
  =СУММ(A2:A10) Суммирует значения в ячейках A2:10.

• 
  =СУММ(A2:A10;C2:C10) Суммирует значения в ячейках A2:10, а также ячейки C2: C10.

СУММ(число1;[число2];…)

Этот раздел содержит некоторые рекомендации по работе с функцией СУММ. Многие из этих рекомендаций можно применить и к другим функциям.

Метод =1+2 или =A+B. Вы можете ввести =1+2+3 или =A1+B1+C2 и получить абсолютно точные результаты, однако этот метод ненадежен по ряду причин.

1. 
   Опечатки. Допустим, вы пытаетесь ввести много больших значений такого вида:

   • 
     =14598,93+65437,90+78496,23

   А теперь попробуйте проверить правильность записей. Гораздо проще поместить эти значения в отдельные ячейки и использовать их в формуле СУММ. Кроме того, значения в ячейках можно отформатировать, чтобы привести их к более наглядному виду, чем если бы они были в формуле.

2. 
   Ошибки #ЗНАЧ!, если ячейки по ссылкам содержат текст вместо чисел

   Допустим, вы используете формулу такого вида:

   Если ячейки, на которые указывают ссылки, содержат нечисловые (текстовые) значения, формула может вернуть ошибку #ЗНАЧ!. Функция СУММ пропускает текстовые значения и выдает сумму только числовых значений.

3. 
   Ошибка #ССЫЛКА! при удалении строк или столбцов

   При удалении строки или столбца формулы не обновляются: из них не исключаются удаленные значения, поэтому возвращается ошибка #ССЫЛКА!. Функция СУММ, в свою очередь, обновляется автоматически.

4. 
   Формулы не обновляют ссылки при вставке строк или столбцов

   При вставке строки или столбца формула не обновляется — в нее не включается добавленная строка, тогда как функция СУММ будет автоматически обновляться (пока вы не вышли за пределы диапазона, на который ссылается формула). Это особенно важно, когда вы рассчитываете, что формула обновится, но этого не происходит. В итоге ваши результаты остаются неполными, и этого можно не заметить.

5. 
   Функция СУММ — отдельные ячейки или диапазоны.

   Используя формулу такого вида:

   вы изначально закладываете в нее вероятность появления ошибок при вставке или удалении строк в указанном диапазоне по тем же причинам. Гораздо лучше использовать отдельные диапазоны, например:

   Такая формула будет обновляться при добавлении и удалении строк.

1. 
   Мне нужно добавить, вычесть, умножить или поделить числа. Просмотрите серию учебных видео: Основные математические операции в Excel или Использование Microsoft Excel в качестве калькулятора.

2. 
   Как уменьшить или увеличить число отображаемых десятичных знаков? Можно изменить числовой формат. Выберите соответствующую ячейку или соответствующий диапазон и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, затем щелкните вкладку Число и выберите нужный формат, указав при этом нужное количество десятичных знаков.

3. 
   Как добавить или вычесть значения времени? Есть несколько способов добавить или вычесть значения времени. Например, чтобы получить разницу между 8:00 и 12:00 для вычисления заработной платы, можно воспользоваться формулой =(«12:00»-«8:00»)*24, т. е. отнять время начала от времени окончания. Обратите внимание, что Excel вычисляет значения времени как часть дня, поэтому чтобы получить суммарное количество часов, необходимо умножить результат на 24. В первом примере используется формула =((B2-A2)+(D2-C2))*24 для вычисления количества часов от начала до окончания работы с учетом обеденного перерыва (всего 8,5 часов).

   
   Если вам нужно просто добавить часы и минуты, вы можете просто вычислить сумму, не умножая ее на 24. Во втором примере используется формула =СУММ(A6:C6), так как здесь нужно просто посчитать общее количество часов и минут, затраченных на задания (5:36, т. е. 5 часов 36 минут).

   Дополнительные сведения см. в статье Сложение и вычитание значений времени

4. 
   Как получить разницу между датами? Как и значения времени, значения дат можно добавить или вычесть. Вот распространенный пример вычисления количества дней между датами. Для этого используется простая формула =B2-A2. При работе со значениями дат и времени важно помнить, что дата или время начала вычитается из даты или времени окончания.

   Другие способы работы с датами описаны в статье Вычисление разности двух дат.

5. 
   Как вычислить сумму только видимых ячеек? Иногда когда вы вручную скрываете строки или используете автофильтр, чтобы отображались только определенные данные, может понадобиться вычислить сумму только видимых ячеек. Для этого можно воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Если вы используете строку итогов в таблице Excel, любая функция, выбранная из раскрывающегося списка «Итог», автоматически вводится как промежуточный итог. Дополнительные сведения см. в статье Данные итогов в таблице Excel.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Дополнительные сведения о функции СУММ

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

Функция СУММЕСЛИМН суммирует только те значения, которые соответствуют нескольким условиям

Функция СЧЁТЕСЛИ подсчитывает только те значения, которые соответствуют одному условию

Функция СЧЁТЕСЛИМН подсчитывает только те значения, которые соответствуют нескольким условиям

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Математические и тригонометрические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Примечание: 
Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Как в Экселе посчитать сумму ячеек

Microsoft Excel – мощный программный инструмент для работы с электронными таблицами. В программе можно составлять таблицы и отчеты, производить расчеты любой сложности, строить диаграммы, графики и многое другое. Довольно часто Эксель используют для того, чтобы вычислить итоговую сумму в столбцах и строках таблицы, или, например, чтобы узнать сумму нескольких ячеек. В этом уроке мы с вами научимся считать сумму ячеек в программе Microsoft Excel благодаря пошаговой инструкции.

Автосумма

Данная функция является одной из самых популярных и комфортных для расчета суммы значений в ячейках таблицы. Для этого нужно сделать следующее:

1. Переходим в вкладку “Главная”, левой кнопкой мыши (далее – ЛКМ) нажимаем на последнюю пустую ячейку столбца или строки, по которой нужно посчитать итоговую сумму и нажимаем кнопку “Автосумма”.
2. Затем в ячейке автоматически заполнится формула расчета суммы.
3. Чтобы получить итоговый результат, нажимаем клавишу “Enter”.
   Чтоб посчитать сумму конкретного диапазона ячеек, ЛКМ выбираем первую и последнюю ячейку требуемого диапазона строки или столбца.
   Далее нажимаем на кнопку “Автосумма” и результат сразу же появится в крайней ячейке столбца или ячейки (в зависимости от того, какой диапазон мы выбрали).
   Данный способ достаточно хорош и универсален, но у него есть один существенный недостаток – он может помочь только при работе с данными, последовательно расположенными в одной строке или столбце, а вот большой объем данных подсчитать таким образом невозможно, равно как и не получится пользоваться “Автосуммой” для отдаленных друг от друга ячеек.
   Допустим, мы выделяем некую область ячеек и нажимаем на “Автосумма”.
   В итоге мы получим не итоговое значение по всем выделенным ячейкам, а сумму каждого столбца или строки по отдельности (в зависимости от того, каким образом мы выделили диапазон ячеек).

Функция “Сумм”

Данная функция в Microsoft Excel позволяет суммировать значения большого массива данных. Для этого необходимо сделать следующее:

1. ЛКМ выделяем ячейку, в которую планируем вывести итоговый результат, далее нажимаем кнопку «Вставить функцию», которая находится с левой стороны строки формул.
2. В открывшемся списке “Построителя формул” находим функцию “СУММ” и нажимаем “Вставить функцию” (или “OK”, в зависимости от версии программы). Чтобы быстро найти нудную функцию можно воспользоваться полем поиском.
3. В появившемся окне по настройке функции указываем имена ячеек, которые необходимо сложить. Разумеется, самостоятельно набирать адрес ячеек можно, но вовсе не обязательно. Вместо этого можно выбрать нужный нам диапазон ячеек, выделив его ЛКМ.
4. Если нужно выбрать еще один диапазон данных, щелкаем ЛКМ на поле “Число 2” и выбираем его аналогично процедуре, описанной выше. Добавлять новые диапазоны можно практически бесконечно, каждый раз нажимая кнопку “+” под последним.
5. После того, как все нужные ячейки выделены, нажимаем кнопку “Готово”.
6. После этого мы видим результат в ранее выбранной ячейке.

Работа с формулами

В программе Эксель можно посчитать сумму с помощью простой формулы сложения. Делается это следующим образом:

1. ЛКМ выделяем ячейку, в которой хотим посчитать сумму. Затем, либо в самой ячейке, либо перейдя в строку формул, пишем знак “=”, ЛКМ нажимаем на первую ячейку, которая будет участвовать в расчетах, после нее пишем знак “+”, далее выбираем вторую, третью и все требуемые ячейки, не забывая между ними проставлять знак “+”.
2. После того, как формула готова, нажимаем “Enter” и получаем результат в выбранной нами ячейке.
   Основным минусом данного способа является то, что сразу отобрать несколько ячеек невозможно, и необходимо указывать каждую по отдельности.

Просмотр суммы в программе Excel.

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

1. Выделяем диапазон ячеек, сумму по которым мы хотим посмотреть.
2. Смотрим вниз на результат, который отображается в строке состояния программы.
   Помимо общей суммы, мы также видим количество выделенных ячеек и среднее значение по ним.

Как Вы могли заметить, программа Эксель позволяет решать задачу суммирования разными способами. Каждый из них имеет свои достоинства и недостатки, свою сложность и продуктивность в зависимости от поставленной задачи и ее специфики.

Как поставить сумму в Excel

При написании формул в документах MS Excel или Word зачастую возникает потребность в написании математического знака суммы (он же «знак суммирования» или «знак нахождения суммы»). Некоторые далекие от математических исчислений люди называют его «знак количества».

Выглядит знак суммы, как угловатая растянутая относительно горизонтальной оси симметрии заглавная русская буква «Е» или английская «E».

Вставить знак суммы в ячейку таблицы «эксель» или на лист «вордовского» документа можно двумя методами, которые приведены ниже:

1. При помощи таблицы символов;
   

2. При помощи опций на панели быстрого доступа в самой программе.
   

Как поставить знак суммы при помощи таблицы символов.

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

Способ нехитрый и не занимает много времени.

Как поставить знак суммы при помощи встроенных возможностей «Эксель».

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

• Выбрать ячейку, в которую поставите знак суммы;
• В меню быстрого доступа выбрать вкладку «Вставка»
• Выбрать пиктограмму «Символ»;
• В появившемся окне выбрать знак суммы и вставить его при помощи кнопки «Вставить»

Подробное пошаговое видео о том, как выполняются описанные выше действия:

Удачи в изучении программы Excel.

Похожее:

Сумма в excel как сделать

Электронные таблицы Эксель являются очень удобным средством для работы с табличными данными. С помощью Экселя табличные данные можно сортировать, выявлять в них характерные закономерности, строить диаграммы различного вида, проводить с данными различные математические действия. Одним из наиболее часто востребованных математических действий является суммирование данных. С помощью Экселя можно буквально за секунды подсчитать сумму в столбце из десятков, сотен и даже тысяч чисел. Рассмотрим как это сделать.

Как найти сумму чисел в Excel Существуют различные способы, позволяющие найти сумму заданных чисел в Эксель, в зависимости от стоящих целей и задач.

1. Самым простым и быстрым способом подсчета суммы чисел в Excel является выделение с помощью мыши диапазона чисел в столбце, для которого необходимо найти сумму. Если использовать мышь вам неудобно, то нажмите на верхнюю ячейку столбца, зажмите клавишу Shift и нажимая стрелку вниз на клавиатуре ↓ выделите числа в столбце. После завершения выделения отпустите клавишу Shift. Сумма будет отражена в нижней части экрана программы наряду с другими показателями: средним значением, общим количеством чисел, минимальным и максимальным значением.

   простой спооб нахождения суммы

2. В том случае, когда нужно отразить сумму какой-либо специальной ячейке, например соответствующей графе итого, можно воспользоваться заданием формулы для нее. Для этого: установите курсор в ячейку, в которой будет отображаться сумма, левее поля для формулы нажмите на значок обозначение функции fx, в появившемся окне мастера функций выберите функцию СУММ.

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

   указание диапазона чисел для суммированияПри указании диапазона чисел можно использовать следующие подстановки:

   • =СУММ(A1:A10) – сумма чисел в диапазоне ячеек от A1 до A10;
   • =СУММ(A1;A10) – сумма чисел в ячейках A1 и A10.
3. Еще одним способом нахождения суммы является использование функции Автосумма. Для этого выделите диапазон суммируемых чисел таким же образом, как в п.1 данной статьи. Перейдите на вкладку Формулы в программе и нажмите кнопку Автосумма. Итоговое значение будет записано в ячейке за последней цифрой в столбце.

   использование автосуммы

Кроме рассмотренных простейших способов суммирования в Excel существуют возможности для более сложных расчетов. Например, сумма чисел, отвечающих определенным признакам и некоторые другие.

Если Вы работаете в программе Excel: строите графики, составляете таблицы, различные отчеты и прочее, то для расчета суммы данных Вам не потребуется искать калькулятор. Сумма чисел в Excel рассчитывается в пару кликов мышкой, и сделать это можно различными способами.

Самый простой – позволит посчитать сумму ячеек, выделите их и посмотрите на строку состояния. Здесь будет указано «Среднее» для всех значений, записанных в выделенные ячейки, «Количество» выделенных ячеек и «Сумма» значений. Выделять также можете пустые ячейки и ячейки с текстом, суммируются только числа.

Если в Вашей таблице есть строка «Итоги» – такой способ не подойдет. Конечно, можно вписать туда число, но если данные для таблицы изменятся, нужно не забыть изменить и значение в ячейке «Итоги». Это не совсем удобно, да и в Эксель можно использовать другие способы, которые будут автоматически пересчитывать сумму в столбце.

Посчитать сумму в столбце можно используя кнопку «Автосумма». Для этого, выделите последнюю ячейку столбца, под значениями. Затем перейдите на вкладку «Формулы» и нажмите «Автосумма». Эксель автоматически выделит верхние ячейки, до первой пустой. Нажмите «Enter», чтобы рассчитать сумму.

Также можете сначала выделить ячейки в столбце, учитывая и пустые, и с текстом – они просто не будут учтены при расчете, а потом нажать «Автосумма». Результат появится в первой пустой ячейке выделенного столбца.

Посчитать сумму в Excel можно используя формулу суммы. Поставьте «=» в нужной ячейке, затем мышкой выделяйте по одной ячейке, нажмите «Enter».

Самый удобный способ для расчета суммы в Эксель – это использование функции СУММ. Поставьте «=» в ячейке, затем наберите «СУММ», откройте скобку «(» и выделите нужный диапазон ячеек. Поставьте «)» и нажмите «Enter».

Также прописать функцию можно прямо в строке формул.

Используя СУММ можно посчитать числа в несмежных ячейках. При этом разделять диапазоны в формуле нужно, используя «;».

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

Как видите, посчитать сумму в Эксель совсем не сложно. Используйте любой из описанных в статье способов.

Поделитесь статьёй с друзьями:

Спасибо за статью.

Excel – мощный редактор для работы с большим объемом данных и ведения различных расчётов при помощи формул. Но как правило, не каждый пользователь знает про все возможности, которые есть в этой программе. Например, как в Excel посчитать сумму в столбце. Некоторые по незнанию используют для этого калькулятор. В данной статье будут рассмотрены различные формулы, которые могут пригодиться большинству офисных работников.

Автосуммирование

Для демонстрации возможностей этой функции, нужно будет сделать следующие действия.

1. Первым делом следует создать какую-нибудь простую таблицу с несколькими значениями. Разместим их всех в один столбик.

2. Затем выделите все ячейки. После этого нажмите на иконку «Суммы», расположенную в правой части панели инструментов.

3. В результате этого редактор автоматически посчитает сумму и выведет ее на следующей строке.

Для того чтобы вывести итоговый результат в отдельном месте, нужно сделать немного иные действия.

1. Кликните на какую-нибудь ячейку.
2. Снова нажмите на иконку «Суммы».

3. В результате этого Эксель предложит сосчитать всё то, что находится выше. Но там ведь находятся и пустые ячейки.

4. Поэтому выделяем только те, которые нам нужны, иначе будут считаться и пустые значения (в момент выделения ваш курсор примет вид белого крестика).

5. Для того чтобы изменения вступили в силу, нужно нажать на клавиатуре на клавишу Enter.

Строка состояния

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

Кроме этого, мы увидим количество элементов и их среднее значение.

Минус данного метода в том, что результат сложения на листе нигде не выводится.

Использование функций

Найти сумму цифр в ячейках можно и при помощи формул. Делается это следующим образом.

1. Кликните на какую-нибудь ячейку.
2. Перейдите в строку ввода уравнений.

3. Введите там следующий текст.

=СУММ(

4. В результате этого у вас появится подсказка, что дальше нужно вводить числа.

5. Укажите там диапазон ячеек, в которых находятся значения. Вводить нужно сначала букву, а потом цифру.

6. Либо вы можете просто выделить эти ячейки.

7. В итоге должно получиться следующее.

=СУММ(C4:C9)

8. Далее нажмите на клавиатуре на клавишу Enter. Только после этого вы сможете узнать результат.

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

Суммирование произведений двух столбцов

Иногда на практике нужно вывести не просто сумму, но еще сделать дополнительные математические действия в разных колонках. Например, как в представленном случае.

Для вывода общей суммы необходимо сделать следующие действия.

1. Сделайте активной нужную ячейку.
2. Кликните на строку ввода формулы.

3. Напишите следующую команду.

=СУММ(

4. Выделите вручную значения в одной из колонок. Например, в нашем случае выделим столбец «Количество».

Если в формуле автоматически не появится закрывающая скобка, ее нужно будет добавить вручную.

5. Затем сдвигаемся в формуле правее и ставим символ «*» (знак умножения). Если вы работаете на ноутбуке, то нужно будет нажать на сочетание клавиш Shift+8.

6. После этого пишем еще раз следующий код.

=СУММ(

7. На этот раз выделяем другой столбец.

8. Обязательно проверяем наличие закрывающей скобки.

Для завершения нужно нажать на клавишу Enter. В результате этого вы увидите сумму произведения двух столбцов.

Сумма с одним условием

Так же бывают случаи, когда нужно складывать не всё подряд, а лишь некоторую часть ячеек. Для этого нужно использовать дополнительные условия, которые будут служить аналогом фильтра значений.

Проверку можно делать как по числовому, так и по текстовому формату данных. Рассмотрим оба случая.

Текстовое значение

1. Для начала добавим в таблице новую строку для вычислений.

2. Перейдите в нужную ячейку. Затем кликните на строку ввода формул.

3. Введите там следующую команду.

=СУММЕСЛИ(

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

5. Выделяем сначала колонку, по которой будем делать проверку. В нашем случае это столбец «Статус», в котором есть два состояния – «пусто» и «продано».

6. После выделения обязательно добавляем символ «;» (точка с запятой), иначе будет ошибка в формуле. Затем пишем слово, по которому будем делать фильтр. В нашем случае это «Продано». Писать необходимо в кавычках. Это обязательно. Разумеется, после этого снова ставим символ «;».

7. Теперь выделяем тот столбец, по которому будут происходить расчеты.

8. Добавляем закрывающую скобку «)».

9. Для проверки работы формулы нажимаем на клавиатуре на клавишу Enter. В результате описанных выше действий должны посчитаться значения только тех ячеек, напротив которых написано слово «Продано».

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

Численное значение

В этом случае для проверки будем использовать графу «Стоимость». Для расчета выполним следующие действия.

1. Добавим еще одну строку, в которой будем выводить «Количество дорогих товаров».

2. Перейдите в нужную ячейку. После этого начните вводить эту же функцию.

=СУММЕСЛИ(

3. В качестве условия выделяем столбец «Стоимость».

4. Обязательно дописываем символ «;». Только после этого можно будет перейти к следующему аргументу.

5. Затем в кавычках указываем «>20». Это условие означает – считать всё то, что больше 20. Здесь используются обычные математические операторы – всё как в школе. В конце снова ставим символ «;».

6. Только теперь можно указать столбец, в котором будут производиться расчёты.

7. Ставим закрывающую скобку «)».

8. Для проверки результата нажимаем на клавиатуре кнопку Enter.

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

Сумма с несколькими условиями

Но иногда всё намного сложней. Например, могут быть случаи, когда нужно посчитать в столбце сумму всех одновременно и дорогих, и проданных продуктов. Рассмотрим, как правильно составить формулу для этой ситуации.

1. Сначала добавим новую строку.

2. Выделяем нужную ячейку и переходим в строку ввода функций.

3. На этот раз вводим другую формулу.

=СУММЕСЛИМН(

Буквы МН означают, что будет использовано много условий.

У этой функции аргументы расположены в ином порядке (в отличие от СУММЕСЛИ).

4. Здесь мы сразу выделяем тот столбец, в котором указано количество товаров. В прошлый раз мы это делали в последнюю очередь – здесь всё наоборот.

5. Обязательно закрываем аргумент точкой с запятой. Затем смотрим на подсказку, что делать дальше.

6. Указываем диапазон первого условия. В нашем случае это графа «Стоимость».

7. Для того чтобы не было ошибок, дописываем символ «;». Затем в двойных кавычках пишем условие сортировки. Например, в нашем случае это «>20». Также закрываем точкой с запятой. Если вы часто работаете в Excel или планируете там работать – нужно привыкать все аргументы закрывать этим символом.

8. Далее мы видим подсказку, что нужно проделать те же самые действия, но теперь для второго условия. Их может быть огромное количество. Просто каждый раз необходимо выделять нужный диапазон и затем указывать условие. Выделяем столбец «Статус».

9. Ставим точку с запятой и пишем в кавычках слово «Продано». В конце нашей формулы обязательно ставим закрывающую скобку.

Для завершения работы с редактором функций нажмите на клавишу Enter. Сразу после этого вы увидите результат.

В нашем случае мы видим, что дорогих товаров у нас 18, а продано из них всего 10.

Заключение

В данной статье были рассмотрены основные возможности редактора Excel для расчета суммы значений в столбцах:

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

Если у вас что-то не получилось, возможно вы делаете что-то не так. Большой процент ошибок многие допускают именно из-за синтаксиса, то есть забывают дописывать кавычки или скобки.

Видеоинструкция

Если у вас что-то не получилось или остались какие-нибудь вопросы, ниже прилагается видеоролик, в котором можно увидеть дополнительные замечания к описанным выше инструкциям.

Как поставить плюс перед числом в Excel

Причина такого поведения заключается в том, что Excel считает плюс избыточными и приводит числа в формат по умолчанию. Но иногда вам может понадобиться добавить знак плюс перед числом. Особенно это важно, когда необходимо показать изменения результата.

Другим распространенным вариантом использования этого является, когда вы хотите знак плюс перед номером телефона. Как сделать телефонный формат подробно описано в этой статье.

Давайте разберем несколько способов добавления знака «+» перед числом

Использование пользовательского числового формата

Вы можете создать собственный числовой формат для ячеек с положительными значениями. Само значение так и останется в числовом формате и вы сможете производить вычисления с этими ячейками. Это просто изменит форматирование и вывод числа на экран.

Предположим, у вас есть набор данных, как показано ниже, и вы хотите добавить знак плюс перед всеми положительными числами:

Пошаговая инструкция как это сделать:

1. Выберите ячейки с числами, в которых необходимо поставить знак плюс.
2. Щелкните правой кнопкой мыши, а затем выберите Формат ячеек
3. В диалоговом окне «Формат ячеек» на вкладке «Число» выберите категорию «Все форматы».
4. В поле Тип введите следующее значение: +# ##0;-# ##0
5. Нажмите на ОК.

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

Обратите внимание, что это фактическое значение ячейки не изменилось. Например, значение в ячейке D2 по-прежнему равно 5100. Оно просто отображается в виде числа со знаком плюс перед ним.

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

Другие полезные пользовательские форматы

Положительные числа со знаком плюс и отрицательными числами в скобках: +# ##0;(# ##0)

Положительные числа со знаком плюс и отрицательными числами красного цвета: +0; [Красный] -0

Положительные числа в зеленом и отрицательные числа в красном: [Зеленый] +0; [Красный] -0

Добавление знака плюс перед положительными числами вручную

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

Добавление апострофа преобразует содержимое ячейки в текст, поэтому, когда вы добавляете знак плюс, он прикрепляется. Кроме того, сам апостроф не отображается на экране или при печати.

Единственный минус такого подхода в том, что дальнейшие вычисления с числом невозможны, так как это уже текстовая ячейка.

Сумма в эксель

В этом руководстве по Excel объясняется, как использовать функцию СУММ в Excel, с синтаксисом и примерами.

Описание

Функция СУММ в Microsoft Excel складывает все числа в диапазоне ячеек и возвращает результат.

Функция СУММ — это встроенная функция Excel, относящаяся к категории математических / триггерных функций. Как функцию рабочего листа, функцию СУММ можно ввести как часть формулы в ячейку рабочего листа.

Синтаксис

Синтаксис функции СУММ в эксел:

SUM (number1, [number2] ,…)

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

В Формуле суммы Excel каждый аргумент может быть положительным или отрицательным числовым значением, диапазоном или ссылкой на ячейку. Например:

=SUM(A1:A100)

=SUM(A1, A2, A5)

=SUM(1,5,-2)

Функция Excel SUM полезна, когда вам нужно сложить значения из разных диапазонов или объединить числовые значения, ссылки на ячейки и диапазоны. Например:

=SUM(A2:A4, A8:A9)

=SUM(A2:A6, A9, 10)

Пример функции СУММ

Давайте посмотрим на несколько примеров функции СУММ в Excel и узнаем, как использовать функцию СУММ в качестве функции рабочего листа в Microsoft Excel:

На изображении выше у нас в столбце А расположены значения, допустим вы хотите сложить значения 2 и 3 строки для этого пишем в ячейке которой вы хотите увидеть результат =СУММ(A2;A3).
Если вы хотите суммировать весь диапазон значений то указывайте его через двоеточие, например в нашем случае это будет =СУММ(A2:A6).

Для того чтобы не писать какие ячейки вы хотите суммировать, вы набираете =СУММ( или выбираете эту функцию в окне «Вставка функции» найти его можно на панели меню выбираете «Формулы»->»Вставка функции»

И щелчком мыши с зажатой клавишей CTRL выбираете ячейки которые хотите суммировать, для выбора диапазона CTRL можно не зажимать.

Cумма столбца в эксел

Если вы хотите посмотреть сумму всего столбца и при этом не выводить ее в таблице, то вам нужно щелкнуть по заголовку столбца левой кнопкой мыши и внизу будет вся нужная вам информация, на изображении ниже показано где смотреть.

Таким же способом можно подсчитать сумму нескольких столбцов, просто выделив нужные вам.

Если столбец, который вы хотите суммировать, имеет переменное количество строк (т. е. Новые ячейки могут быть добавлены, а существующие могут быть удалены в любое время), вы можете просуммировать весь столбец, указав ссылку на столбец, без указания нижней или верхней границы , Например: =SUM(B:B), но в этом случае вы не должны помещать формулу «Сумма столбца» в столбец, который вы хотите суммировать потому что это создаст циклическую ссылку на ячейку, и ваша формула СУММ вернет 0.

Автосумма столбца

Если в конце столбца вам необходимо вывести сумму этого столбца то вам необходимо выполнить следующие шаги:

1. Выделить ячейку в которой будет сумма строк выше
2. Выбираем в меню «Главная» значек Сумма

3. После выбора программа выделяет нужный диапазон, проверяем все ли правильно и нажимаем Enter.

Более быстрый способ выполнить автосумму в Excel — использовать 

Alt + =, 

Просто удерживайте клавишу Alt, нажмите клавишу со знаком равенства, а затем нажмите Enter, чтобы завершить автоматически вставленную формулу суммы.

Сумма строки эксел

Чтобы суммировать всю строку с неопределенным числом столбцов, укажите ссылку на всю строку в формуле Excel СУММ, например:

=СУММ(2:2)

Помните, что вы не должны вводить эту формулу «Сумма строки» в любой ячейке той же строки, чтобы избежать создания циклической ссылки, поскольку это приведет к неправильному вычислению, если таковое имеется:

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

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

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

Хорошо то, что функция СУММЕСЛИ идентична во всех версиях Excel с 2016 по 2003 год. Еще одна замечательная новость заключается в том, что если вы потратите некоторое время на изучение СУММЕСЛИ, вам потребуется совсем немного усилий, чтобы понять других. Функции «ЕСЛИ», такие как СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИ, СРЕДНЕЛИ и т.д.

СУММЕСЛИ в Excel — синтаксис и использование

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

Если вам довелось прочитать учебник «СЧЁТЕСЛИ» в этом блоге, у вас не возникнет проблем с пониманием СУММЕСЛИ в Excel, поскольку его синтаксис и использование аналогичны.Итак, синтаксис функции СУММЕСЛИ:

СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])

Как видите, функция СУММЕСЛИ имеет 3 аргумента — первые 2 обязательны, а 3 ^rd необязательны.

1. диапазон — диапазон ячеек, которые будут оцениваться по вашим критериям, например A1: A10.
2. критерии — условие, которое необходимо выполнить. Критерии могут быть предоставлены в форме числа, текста, даты, логического выражения, ссылки на ячейку или другой функции Excel.Например, вы можете ввести такие критерии, как «5», «вишня», «25.10.2014», «<5», «A1» и т. Д.

   Примечание. Обратите внимание, что любые текстовые критерии или критерии, содержащие математические символы, должны быть заключены в двойные кавычки («). Для числовых критериев двойные кавычки не требуются.

3. диапазон_суммы — ячейки для суммирования при выполнении условия. Этот аргумент является необязательным, и вам нужно использовать его только в том случае, если вы хотите суммировать ячейки, отличные от определенных в аргументе диапазона.Если аргумент sum_range опущен, Excel будет суммировать те же ячейки, к которым применяются критерии (т.е. указанные в аргументе диапазона).

   Чтобы лучше проиллюстрировать синтаксис Excel СУММЕСЛИ, рассмотрим следующий пример. Предположим, у вас есть список продуктов в столбце A и соответствующие суммы в столбце C. Вы хотите знать сумму всех сумм, относящихся к данному продукту, например бананы.

   Теперь давайте определим аргументы для нашей формулы СУММЕСЛИ:

   • диапазон: A2: A8
   • критерий: «бананы»
   • диапазон сумм: C2: C8

   Собирая все вместе, получаем: = СУММЕСЛИ (A2: A8, «бананы», C2: C8)

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

   = СУММЕСЛИ (A2: A8, F1, C2: C8)

   Примечание. Параметр sum_range фактически указывает только крайнюю левую верхнюю ячейку диапазона для суммирования. Оставшаяся область определяется размерами аргумента диапазона.

На практике это означает, что аргумент sum_range не обязательно должен быть того же размера, что и аргумент диапазона, т.е.е. в нем может быть разное количество строк и столбцов. Однако первая ячейка (то есть верхняя левая ячейка ) диапазона суммы всегда должна быть правой. Например, в приведенной выше формуле СУММЕСЛИ можно указать C2, C2: C4 или даже C2: C100 в качестве аргумента диапазон_суммы, и результат все равно будет тем же. Однако лучше всего использовать диапазон и сумму_диапазона одинакового размера.

Дело в том, что Microsoft Excel не полагается на способность пользователя предоставить диапазон соответствия и параметры sum_range, поэтому, чтобы избежать возможных проблем с несогласованностью и предотвратить ошибки, он определяет диапазон суммы самостоятельно следующим образом.Он принимает верхнюю левую ячейку в аргументе sum_range в качестве начальной ячейки (C2 в нашем примере SUMIF в Excel), а затем включает столько столбцов и строк, сколько содержится в аргументе диапазона (в нашем случае это 1 столбец x 7 строк, т. Е. C2: C8).

Как использовать СУММЕСЛИ в Excel — примеры формул

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

СУММЕСЛИ больше, меньше или равно

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

Примечание. Обратите внимание, что в формулах Excel СУММЕСЛИ оператор сравнения, за которым следует число или текст, всегда следует заключать в двойные кавычки («»).

Как использовать СУММЕСЛИ в Excel с текстовыми критериями

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

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

Дополнительные сведения о частичном совпадении см. В примерах СУММЕСЛИ с подстановочными знаками.

А теперь давайте посмотрим на точную формулу «Сумма, если не равно» в действии. Как показано на скриншоте ниже, он добавляет запасы всех товаров, кроме "бананов голдфингер":

= СУММЕСЛИ (A2: A7, «<> бананы голдфингер», C2: C7)

Примечание. Как и большинство других функций Excel, СУММЕСЛИ нечувствительна к регистру, что означает, что «<> бананы», «<> бананы» и «<> бананы» будут давать точно такой же результат.

Использование операторов сравнения со ссылками на ячейки

Если вы хотите сделать еще один шаг и получить более универсальную формулу СУММЕСЛИ в Excel, вы можете заменить числовое или текстовое значение в критериях ссылкой на ячейку, например:
= СУММЕСЛИ (A2: A8, "<>" & F1, C2: C8)

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

Примечание. Когда вы используете логическое выражение со ссылкой на ячейку, вы должны использовать двойные кавычки ("") для начала текстовой строки и амперсанд (&) для объединения и завершения строки, например "<>" & F1.

Оператор «равно» (=) можно опустить перед ссылкой на ячейку, поэтому обе приведенные ниже формулы эквивалентны и верны:

Формула 1: = СУММЕСЛИ (A2: A8, "=" & F1, C2: C8)

Формула 2: = СУММЕСЛИ (A2: A8, F1, C2: C8)

Формулы СУММЕСЛИ с подстановочными знаками

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

Нам доступны следующие подстановочные знаки:

• Звездочка ( * ) - представляет любое количество символов
• Вопросительный знак (?) - представляет собой один символ в определенной позиции.

Пример 1. Сумма значений на основе частичного совпадения

Предположим, вы хотите просуммировать суммы, относящиеся ко всем сортам бананов. Следующие формулы СУММЕСЛИ подойдут для вас:

= СУММЕСЛИ (A2: A8, «* бананы *», C2: C8) - критерий включает текст, заключенный в звездочки (*).

= СУММЕСЛИ (A2: A8, "*" & F1 & "*", C2: C8) - критерий включает ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для объединения строка.

Если вы хотите подсчитать только те ячейки, которые начинаются или заканчиваются определенным текстом, добавьте только один * до или после текста:

= СУММЕСЛИ (A2: A8, «бананы *», C2: C8) - сумма значений в C2: C8, если соответствующая ячейка в столбце A начинается со слова «бананы».

= СУММЕСЛИ (A2: A8, «* бананы», C2: C8) - сумма значений в C2: C8, если соответствующая ячейка в столбце A заканчивается словом «бананы».

Наконечник. Чтобы использовать подстановочные знаки со ссылками на ячейки , объедините их с помощью оператора конкатенации (&). В этом примере вы также можете использовать следующую формулу для суммирования всех "бананов" на складе: = СУММЕСЛИ (A2: A8, "*" & F1 & "*", C2: C8)

Пример 2. Сумма значений с заданным количеством символов

Если вы хотите суммировать некоторые значения, состоящие ровно из 6 букв, вы должны использовать следующую формулу:

= СУММЕСЛИ (A2: A8, «??????», C2: C8)

Пример 3.Ячейки суммы, соответствующие текстовым значениям

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

= СУММЕСЛИ (A2: A8, «? *», C2: C8) - складывает значения в ячейках C2: C8, если соответствующая ячейка в столбце A содержит хотя бы 1 символ.

= СУММЕСЛИ (A2: A8, «*», C2: C8) - включает кажущиеся пустыми ячейки, содержащие строки нулевой длины, возвращаемые некоторыми другими формулами, например.г. = "" .

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

Пример 4. Лечить * или? как обычно иероглифы

Если вы хотите либо *, либо? чтобы обрабатываться как литерал, а не как подстановочный знак, используйте предшествующую тильду (~). Например, следующая формула СУММЕСЛИ добавит значения в ячейки C2: C8, если ячейка в столбце A в той же строке содержит вопросительный знак:

= СУММЕСЛИ (A2: A8, «~?», C2: C8)

Суммирование наибольшего или наименьшего числа в диапазоне

Чтобы сложить наибольшее или наименьшее число в диапазоне, используйте функцию СУММ вместе с функцией НАИБОЛЬШИЙ или МАЛЕНЬКИЙ соответственно.

Пример 1. Складываем несколько наибольших / наименьших чисел

Если вы хотите просуммировать несколько чисел, например 5, вы можете ввести их прямо в формулу, например:

= СУММ (НАИБОЛЬШИЙ (B1: B10, {1,2,3,4,5})) - суммировать 5 самых больших чисел

= СУММ (МАЛЕНЬКИЙ (B1: B10, {1,2,3,4,5})) - сумма 5 наименьших чисел

Примечание. Если есть 2 или более номеров, которые занимают последнее место, будет включен только первый. Как видите, вторые 9 не складываются в приведенном выше примере.

Пример 2. Сложить много верхних / нижних чисел

Если вы хотите сложить много чисел, вместо того, чтобы перечислять их все в формуле, вы можете вложить функции СТРОКА и КОСВЕННО в формулу СУММ. В функции ДВССЫЛ используйте номера строк, которые представляют количество значений, которые вы хотите сложить. Например, следующие формулы суммируют верхние и нижние 15 чисел соответственно:

= СУММ (БОЛЬШОЙ (B1: B50, СТРОКА (КОСВЕННАЯ ("1:15"))))

= СУММ (МАЛЕНЬКИЙ (B1: B50; СТРОКА (КОСВЕННАЯ ("1:15"))))

Поскольку это формулы массива, не забудьте ввести их в виде массива, нажав Ctrl + Shift + Enter.

Пример 3. Суммировать переменное количество наибольших / наименьших значений

Если вы не хотите изменять формулу каждый раз, когда вы хотите суммировать другое количество ячеек, вы можете поместить это число в какую-либо ячейку вместо того, чтобы вводить его непосредственно в формулу. Затем вы можете использовать функции СТРОКА и ДВССЫЛ, как показано выше, и обратиться к ячейке, содержащей переменную, в нашем случае ячейке E1:

= SUM (LARGE (B1: B50, ROW (INDIRECT ("1:" & E1)))) - суммирует переменное количество верхних значений

= SUM (SMALL (B1: B50, ROW (INDIRECT ("1:" & E1)))) - суммирует переменное количество нижних значений

Помните, что это формулы массива, поэтому вам нужно нажать Ctrl + Shift + Enter, чтобы завершить их.

Как суммировать ячейки, соответствующие пустым ячейкам

Если «пусто» означает ячейки, которые не содержат абсолютно ничего - ни формулы, ни строки нулевой длины, возвращаемой какой-либо другой функцией Excel, тогда используйте "=" в качестве критерия, как в следующей формуле СУММЕСЛИ:

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

Если "blank" включает строки нулевой длины (например, ячейки с формулой = "" ), тогда используйте "" в качестве критерия:

= СУММЕСЛИ (A2: A10, "", C2: C10)

Обе приведенные выше формулы оценивают ячейки в столбце A, и если обнаруживаются пустые ячейки, добавляются соответствующие значения из столбца C.

Как суммировать ячейки, соответствующие непустым ячейкам

Если вы хотите просуммировать значения ячеек в столбце C, когда соответствующая ячейка в столбце A не пуста, используйте «<>» в качестве критерия в формуле СУММЕСЛИ:

= СУММЕСЛИ (A2: A10; «<>»; C2: C10)

В приведенной выше формуле подсчитываются значения, соответствующие всем непустым ячейкам, включая строки нулевой длины.

Как использовать СУММЕСЛИ в Excel с датами

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

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

Если вы хотите суммировать значения на основе текущей даты, вам нужно использовать СУММЕСЛИ в Excel в сочетании с функцией СЕГОДНЯ (), как показано ниже:

На снимке экрана ниже показано, как можно использовать последнюю формулу, чтобы найти общее количество всех продуктов, отправленных за неделю.

Как суммировать значения в заданном диапазоне дат

На форумах и в блогах, посвященных Excel, часто задают следующий вопрос - «Как суммировать две даты?»

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

= СУММЕСЛИ (B2: B9, «> = 10/1/2014», C2: C9) - СУММЕСЛИ (B2: B9, «> = 11/1/2014», C2: C9)

Эта формула суммирует значения в ячейках C2: C9, если дата в столбце B находится между 1 октября 2014 г. и 31 октября 2014 г. включительно.

Эта формула на первый взгляд может показаться немного сложной, но при более внимательном рассмотрении она кажется довольно простой.Первая функция СУММЕСЛИ складывает все ячейки в C2: C9, где соответствующая ячейка в столбце B больше или равна дате начала (Oct-1 в этом примере). Затем вам просто нужно вычесть все значения, которые выпадают после даты окончания (31 октября), которые возвращаются второй функцией СУММЕСЛИ.

Как суммировать значения в нескольких столбцах

Чтобы лучше понять проблему, давайте рассмотрим следующий пример. Предположим, у вас есть сводная таблица ежемесячных продаж. Поскольку он был консолидирован из нескольких региональных репостов, для одного и того же продукта существует несколько записей:

Итак, как вы определяете общее количество яблок, проданных во всех штатах за последние три месяца?

Как вы помните, размеры sum_range определяются размерами параметра range.Вот почему вы не можете использовать такую ​​формулу, как = СУММЕСЛИ (A2: A9, «яблоки», C2: E9) , потому что она суммирует значения, соответствующие «Яблокам», только в столбце C. Это не то, что мы ищем, правда?

Самое логичное и простое решение, которое напрашивается само собой, - это создать вспомогательный столбец, который вычисляет отдельные промежуточные итоги для каждой строки, а затем ссылаться на этот столбец в критерии sum_range.

Поместите простую формулу СУММ в ячейку F2, затем заполните столбец F: = СУММ (C2: E2)

После этого вы можете написать обычную формулу СУММЕСЛИ, например:

= СУММЕСЛИ (A2: A9, «яблоки», F2: F9)
или
= СУММЕСЛИ (A2: A9, h2, F2: F9)

В приведенных выше формулах sum_range точно того же размера, что и range, т.е.е. 1 столбец и 8 строк, поэтому они возвращают правильный результат:

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

= СУММ (СУММЕСЛИ (A2: A9, I1, C2: C9), СУММЕСЛИ (A2: A9, I1, D2: D9), СУММЕСЛИ (A2: A9, I1, E2: E9))

Другой способ - использовать более сложную формулу массива (не забудьте нажать Ctrl + Shift + Enter):
{= SUM ((C2: C9 + D2: D9 + E2: E9) * (- (A2: A9 = I1)))}

Обе приведенные выше формулы вернут в нашем случае 2070.

Почему моя формула СУММЕСЛИ не работает?

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

1. Параметры range и sum_range должны быть диапазонами, а не массивами

Первый (диапазон) и третий (диапазон_суммирования) параметры вашей формулы СУММЕСЛИ всегда должны быть диапазонами.Если вы попытаетесь передать что-нибудь еще, например массив типа {1,2,3}, Excel выдаст сообщение об ошибке.

Правильная формула: = СУММЕСЛИ (A1: A3, «цветок», C1: C3)

Неправильная формула: = СУММЕСЛИ ({"цветок", "дерево", "куст"}, "цветок", C1: C3)

2. Как суммировать значения из других листов или книг

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

Например, следующая формула суммирует значения в ячейках F2: F9 на листе 1 книги 1, если соответствующая ячейка в столбце A, если тот же лист содержит «яблоки»:

= СУММЕСЛИ ([Книга1.xlsx] Sheet1! $ A $ 2: $ A $ 9, "яблоки", [Book1.xlsx] Sheet1! $ F $ 2: $ F $ 9)

Однако эта формула не будет работать, как только Книга 1 будет закрыта. Это происходит из-за того, что диапазоны, на которые ссылаются формулы СУММЕСЛИ в закрытых книгах, лишаются ссылок на массивы, а поскольку в аргументах диапазон и диапазон_суммирования массивы не допускаются, формула СУММЕСЛИ выдаст #VALUE! ошибка.

3. Чтобы избежать проблем, убедитесь, что диапазон и сумма_диапазона имеют одинаковый размер

Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel параметры range и sum_range не обязательно должны быть одинакового размера.В Excel 2000 и более ранних версиях range и sum_range разного размера могут вызывать проблемы. Однако даже в самых последних версиях Excel 2010 и Excel 2016 сложные формулы СУММЕСЛИ, в которых диапазон_суммирования имеет меньше строк и / или столбцов, чем диапазон, являются капризными. Вот почему считается хорошей практикой всегда иметь аргументы range и sum_range одного размера и формы.

4. Как сделать так, чтобы формулы СУММЕСЛИ работали быстрее

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

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

Вас также может заинтересовать

.

СУММ (функция СУММ) - служба поддержки Office

В этом разделе обсуждаются некоторые передовые методы работы с функцией СУММ. Многое из этого можно применить и к работе с другими функциями.

Метод = 1 + 2 или = A + B - хотя вы можете ввести = 1 + 2 + 3 или = A1 + B1 + C2 и получить полностью точные результаты, эти методы подвержены ошибкам по нескольким причинам:

1. Опечатки - Представьте, что вы пытаетесь ввести больше и / или намного большие значения, например:

   Затем попробуйте проверить правильность ввода.Гораздо проще поместить эти значения в отдельные ячейки и использовать формулу СУММ. Кроме того, вы можете форматировать значения, когда они находятся в ячейках, что сделает их более удобочитаемыми, чем когда они находятся в формуле.

2. #VALUE! ошибки ссылки на текст вместо чисел

   Если вы используете такую ​​формулу:

   Ваша формула может нарушиться, если в указанных ячейках есть какие-либо нечисловые (текстовые) значения, которые вернут #VALUE! ошибка.SUM проигнорирует текстовые значения и выдаст вам сумму только числовых значений.

3. # ССЫЛКА! ошибка при удалении строк или столбцов

   Если вы удалите строку или столбец, формула не будет обновляться, чтобы исключить удаленную строку, и она вернет #REF! ошибка, при которой функция СУММ обновляется автоматически.

4. Формулы не обновляют ссылки при вставке строк или столбцов

   Если вы вставляете строку или столбец, формула не будет обновляться, чтобы включить добавленную строку, при этом функция СУММ будет обновляться автоматически (если вы не находитесь за пределами диапазона, указанного в формуле).Это особенно важно, если вы ожидаете, что ваша формула обновится, но этого не произошло, поскольку в результате вы получите неполные результаты, которые вы можете не заметить.

5. СУММ с отдельными ссылками на ячейки в сравнении с диапазонами

   Используя формулу вида:

   Также подвержен ошибкам при вставке или удалении строк в указанном диапазоне по тем же причинам.Намного лучше использовать отдельные диапазоны, например:

   Что будет обновляться при добавлении или удалении строк.

.

Excel СУММЕСЛИМН и СУММЕСЛИ с несколькими критериями - примеры формул

В этом руководстве объясняется разница между функциями СУММЕСЛИ и СУММЕСЛИМН с точки зрения их синтаксиса и использования, а также приводится ряд примеров формул для суммирования значений с несколькими критериями И / ИЛИ в Excel 2019, 2016, 2013, 2010, 2007, 2003 и ниже.

Как всем известно, Microsoft Excel предоставляет набор функций для выполнения различных вычислений с данными. Несколько статей назад мы изучили СЧЁТЕСЛИ и СЧЁТЕСЛИМН, которые предназначены для подсчета ячеек на основе одного условия и нескольких условий соответственно.На прошлой неделе мы рассмотрели СУММЕСЛИ в Excel, который добавляет значения, соответствующие указанным критериям. Теперь пора перейти к версии СУММЕСЛИ во множественном числе - СУММЕСЛИМН Excel, которая позволяет суммировать значения по нескольким критериям.

Те, кто знаком с функцией СУММЕСЛИ, могут подумать, что для ее преобразования в СУММЕСЛИМН требуется просто дополнительная буква "S" и несколько дополнительных критериев. Это могло бы показаться вполне логичным… но «логично» это не всегда так при работе с Microsoft 🙂

Функция СУММЕСЛИ в Excel - синтаксис и использование

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

СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])

• диапазон - диапазон ячеек, которые будут оцениваться по вашим критериям, обязательный.
• критерии - условие, которое должно быть выполнено, обязательное.
• диапазон_суммы - ячейки для суммирования, если условие выполнено, необязательно.

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

Функция СУММЕСЛИМН в Excel - синтаксис и использование

СУММЕСЛИМН в Excel используется для поиска условной суммы значений на основе нескольких критериев . Функция СУММЕСЛИМН была представлена ​​в Excel 2007, поэтому вы можете использовать ее во всех современных версиях Excel 2019, 2016, 2013, 2010 и 2007.

По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:

СУММЕСЛИМН (диапазон_сумм, диапазон_критерия1, критерий1, [диапазон_критериев2, критерий2],…)

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

• диапазон_суммы - одна или несколько ячеек для суммирования, требуется. Это может быть отдельная ячейка, диапазон ячеек или именованный диапазон. Суммируются только ячейки с цифрами; пустые и текстовые значения игнорируются.
• диапазон_критерия1 - первый диапазон, который должен быть оценен по связанным критериям, обязательный.
• критерий1 - первое условие, которое должно быть выполнено, обязательно. Вы можете указать критерии в форме числа, логического выражения, ссылки на ячейку, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, «> = 10», A1, «вишня» или СЕГОДНЯ ().
• диапазон_критериев2, критерии2,… - это дополнительные диапазоны и критерии, связанные с ними, необязательно.В формулах СУММЕСЛИМН можно использовать до 127 пар диапазон / критерий.

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

А теперь давайте взглянем на формулу Excel СУММЕСЛИМН с двумя условиями. Предположим, у вас есть таблица, в которой перечислены партии фруктов от разных поставщиков. У вас есть названия фруктов в столбце A, названия поставщиков в столбце B и количество в столбце C.Вы хотите узнать сумму сумм, относящихся к данному фрукту и поставщику, например все яблоки поставил Пит.

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

• диапазон сумм - C2: C9
• диапазон_критерия1 - A2: A9
• критерий1 - «яблоки»
• диапазон_критерия2 - B2: B9
• критерий2 - «Пит»

Теперь соберите указанные выше параметры, и вы получите следующую формулу СУММЕСЛИМН:

= СУММЕСЛИМН (C2: C9, A2: A9, «яблоки», B2: B9, «Пит»)

Для дальнейшего уточнения формулы можно заменить текстовые критерии «яблоки» и «Пит» ссылками на ячейки.В этом случае вам не придется изменять формулу для расчета количества других фруктов от другого поставщика:

= СУММЕСЛИМН (C2: C9, A2: A9, F1, B2: B9, F2)

Использование СУММЕСЛИМН и СУММЕСЛИ в Excel - что нужно помнить

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

Хотя общая часть понятна - схожее назначение и параметры - различия не так очевидны, но очень существенны.

1. Порядок аргументов

В функциях СУММЕСЛИ и СУММЕСЛИМН в Excel порядок аргументов разный. В частности, диапазон_суммирования - это 1-й параметр в СУММЕСЛИМН, но это 3 ^rd в СУММЕСЛИ.

На первый взгляд может показаться, что Microsoft намеренно усложняет обучение своих пользователей. Однако при ближайшем рассмотрении вы увидите причину этого.Дело в том, что sum_range не является обязательным в SUMIF. Если вы его опустите, нет проблем, ваша формула СУММЕСЛИ будет суммировать значения в диапазоне (первый параметр).

В СУММЕСЛИМН диапазон сумм очень важен и обязателен, поэтому он стоит на первом месте. Возможно, ребята из Microsoft подумали, что после добавления пары диапазон / критерий 10 ^-й или 100 ^-й кто-то может забыть указать диапазон для суммирования 🙂

2. Диапазон сумм и диапазон критериев должны быть одинакового размера

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

Например, = СУММЕСЛИ (A2: A9, F1, C2: C18) вернет правильный результат, потому что Excel учитывает только крайнюю левую ячейку в аргументе диапазон_суммы (C2 в этом примере, что является правильным), и затем включает столько столбцов и строк, сколько содержится в аргументе диапазона.

Формула СУММЕСЛИМН = СУММЕСЛИМН (C2: C9, A2: A9, «яблоки», B2: B10, «Пит») вернет #VALUE! ошибка, поскольку диапазон_критерия2 (B2: B10) не совпадает по размеру диапазон_критерия1 (A2: A9) и диапазон_суммы (C2: C9).

Хорошо, хватит стратегии (т.е. теории), давайте перейдем к тактике (т.е. примерам формул:)

Как использовать СУММЕСЛИМН в Excel - примеры формул

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

Пример 1. СУММЕСЛИМН в Excel с операторами сравнения

Предположим, в нашей таблице поставщиков фруктов вы хотите просуммировать все поставки Майка с Кол-во.200 или больше. Для этого вы используете оператор сравнения «больше или равно» (> =) в критерии2 и получаете следующую формулу СУММЕСЛИМН:

= СУММЕСЛИМН (C2: C9, B2: B9, «Майк», C2: C9, «> = 200»)

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

Мы подробно рассмотрели все возможные операторы сравнения при обсуждении функции СУММЕСЛИ в Excel, те же операторы могут использоваться в критериях СУММЕСЛИМН.Например, следующая формула возвращает сумму всех значений в ячейках C2: C9, которые больше или равны 200 и меньше или равны 300.

= СУММЕСЛИМН (C2: C9, C2: C9, «> = 200», C2: C9, «<= 300»)

Пример 2. Использование СУММЕСЛИМН в Excel с датами

Если вы хотите суммировать значения с несколькими критериями на основе текущей даты, используйте функцию СЕГОДНЯ () в критерии СУММЕСЛИМН, как показано ниже. Следующая формула суммирует значения в столбце D, если соответствующая дата в столбце C приходится на последние 7 дней, включая сегодняшний день:

= СУММЕСЛИМН (D2: D10, C2: C10, "> =" & СЕГОДНЯ () - 7, C2: C10, "<=" & СЕГОДНЯ ())

Примечание. Когда вы используете другую функцию Excel вместе с логическим оператором в критериях, вы должны использовать амперсанд (&) для объединения строки, например «<=» & TODAY ().

Аналогичным образом вы можете использовать функцию СУММЕСЛИ в Excel для суммирования значений в заданном диапазоне дат. Например, следующая формула СУММЕСЛИМН добавляет значения в ячейках C2: C9, если дата в столбце B приходится на период с 1 октября 2014 года по 31 октября 2014 года включительно.

= СУММЕСЛИМН (C2: C9, B2: B9, «> = 01.10.2014», B2: B9, «<= 31.10.2014»)

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

Пример 3. СУММЕСЛИМН в Excel с пустыми и непустыми ячейками

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

А теперь давайте посмотрим, как можно использовать формулу СУММЕСЛИМН с «пустыми» и «непустыми» критериями для реальных данных.

Предположим, у вас есть дата заказа в столбце B, дата доставки в столбце C и Qty.в столбце D. Как найти общее количество продуктов, которые еще не были доставлены? То есть вы хотите узнать сумму значений, соответствующих непустым ячейкам в столбце B и пустым ячейкам в столбце C.

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

= СУММЕСЛИМН (D2: D10, B2: B10, «<>», C2: C10, «=»)

Использование СУММЕСЛИ в Excel с несколькими критериями ИЛИ

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

Пример 1. СУММЕСЛИ + СУММЕСЛИ

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

= СУММЕСЛИ (C2: C9, «Майк», D2: D9) + СУММЕСЛИ (C2: C9, «Джон», D2: D9)

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

Пример 2. СУММ и СУММЕСЛИ с аргументом массива

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

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

В предыдущем примере, если вы хотите суммировать продукты, доставленные Джоном, Майком и Питом, критерии вашего массива будут выглядеть как {"Джон", "Майк", "Пит"}. И полная функция СУММЕСЛИ составляет СУММЕСЛИ (C2: C9, {"Джон", "Майк", "Пит"}, D2: D9) .

Аргумент массива, состоящий из 3 значений, заставляет вашу формулу СУММЕСЛИ возвращать три отдельных результата, но поскольку мы пишем формулу в одной ячейке, она вернет только первый результат - то есть общее количество продуктов, доставленных Джоном.Чтобы заставить этот подход с использованием критериев массива работать, вам нужно использовать еще один небольшой трюк - заключить формулу СУММЕСЛИ в функцию СУММ, например:

= СУММ (СУММЕСЛИ (C2: C9, {"Джон", "Майк", "Пит"}, D2: D9))

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

Этот подход работает как с числами, так и с текстовыми значениями. Например, если вместо названий поставщиков в столбце C у вас были идентификаторы поставщиков, такие как 1, 2, 3 и т. Д., тогда ваша формула СУММЕСЛИ будет выглядеть примерно так:

= СУММ (СУММЕСЛИ (C2: C9, {1,2,3}, D2: D9))

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

Пример 3. СУММПРОИЗВ и СУММЕСЛИ

В случае, если вы предпочитаете перечислить критерии в некоторых ячейках, а не указывать их непосредственно в формуле, вы можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в заданных массивах и возвращает сумму этих продуктов.

= СУММПРОИЗВ (СУММЕСЛИ (C2: C9, G2: G4, D2: D9))

Где G2: G4 - это ячейки, содержащие ваши критерии, в нашем случае имена поставщиков, как показано на скриншоте ниже.

Но, конечно, ничто не мешает вам перечислить значения в критериях массива вашей функции СУММЕСЛИ, если вы хотите:

= СУММПРОИЗВ (СУММЕСЛИ (C2: C9, {"Майк", "Джон", "Пит"}, D2: D9))

Результат, возвращаемый обеими формулами, будет идентичен тому, что вы видите на скриншоте:

СУММЕСЛИМН в Excel с несколькими критериями ИЛИ

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

Как обычно, пример может помочь лучше проиллюстрировать суть дела. В нашу таблицу поставщиков фруктов добавим дату доставки (столбец E) и найдем общее количество, доставленное Майком, Джоном и Питом в октябре.

Пример 1. СУММЕСЛИМН + СУММЕСЛИМН

Формула, полученная при таком подходе, включает в себя много повторений и выглядит громоздкой, но ее легко понять и, что самое главное, она работает 🙂

= СУММЕСЛИМН (D2: D9, C2: C9, «Майк», E2: E9, «> = 10/1/2014», E2: E9, «<= 31.10.2014») +
СУММЕСЛИМН (D2 : D9, C2: C9, «Джон», E2: E9, «> = 01.10.2014», E2: E9, «<= 31.10.2014») +
СУММЕСЛИМН (D2: D9, C2: C9 , "Пит", E2: E9, "> = 01.10.2014", E2: E9, "<= 31.10.2014")


Как видите, вы пишете отдельную функцию СУММЕСЛИМН для каждого из поставщиков и включаете два условия - равное или больше 1 октября ("> = 10/1/2014",) и меньше или равно 31 октября ( «<= 31.10.2014»), а затем суммируете результаты.

Пример 2. СУММ и СУММЕСЛИМН с аргументом массива

Я попытался объяснить суть этого подхода на примере СУММЕСЛИ, поэтому теперь мы можем просто скопировать эту формулу, изменить порядок аргументов (как вы помните, он отличается в СУММЕСЛИ и СУММЕСЛИМН) и добавить дополнительные критерии. Полученная формула более компактна, чем СУММЕСЛИМН + СУММЕСЛИМН:

.

= СУММ (СУММЕСЛИМН (D2: D9, C2: C9, {"Майк", "Джон", "Пит"}, E2: E9, "> = 01.10.2014", E2: E9, "<= 31.10.2014 "))

Результат, возвращаемый этой формулой, точно такой же, как на скриншоте выше.

Пример 3. СУММПРОИЗВ и СУММЕСЛИМН

Как вы помните, подход СУММПРОИЗВ отличается от двух предыдущих тем, что вы вводите каждый из критериев в отдельной ячейке, а не указываете их непосредственно в формуле. В случае нескольких наборов критериев функции СУММПРОИЗВ будет недостаточно, и вам также придется использовать ЕЧИСЛО и ПОИСКПОЗ.

Итак, если предположить, что имена расходных материалов находятся в ячейках h2: h4, дата начала находится в ячейке h5, а дата окончания - в ячейке H5, наша формула СУММПРОИЗВ принимает следующую форму:

= СУММПРОИЗВ (- (E2: E9> = h5), - (E2: E9 <= H5), - (ISNUMBER (MATCH (C2: C9, h2: h4,0))), D2: D9 )

Многие задаются вопросом, зачем использовать двойное тире (-) в формулах СУММПРОИЗВ.Дело в том, что Excel SUMPRODUCT игнорирует все значения, кроме числовых, в то время как операторы сравнения в нашей формуле возвращают логические значения (TRUE / FALSE), которые не являются числовыми. Чтобы преобразовать эти логические значения в 1 и 0, вы используете двойной знак минус, который технически называется двойным унарным оператором. Первый унарный переводит TRUE / FALSE в значение -1/0 соответственно. Вторая унарная инвертирует значения, то есть меняет знак, превращая их в +1 и 0, что может понять функция СУММПРОИЗВ.

Я надеюсь, что приведенное выше объяснение имеет смысл. И даже если это не так, просто запомните это эмпирическое правило - используйте двойной унарный оператор (-), когда вы используете операторы сравнения в своих формулах СУММПРОИЗВ.

Использование Excel SUM в формулах массива

Как вы помните, Microsoft реализовала функцию СУММЕСЛИМН в Excel 2007. Если кто-то по-прежнему использует Excel 2003, 2000 или более раннюю версию, вам придется использовать формулу массива СУММ для добавления значений с несколькими критериями И. Естественно, этот подход работает и в современных версиях Excel 2013–2007 и может считаться старомодным аналогом функции СУММЕСЛИМН.

В формулах СУММЕСЛИ, описанных выше, вы уже использовали аргументы массива, но формула массива - это нечто иное.

Пример 1. Сумма с несколькими критериями И в Excel 2003 и ранее

Вернемся к самому первому примеру, где мы обнаружили сумму сумм, относящихся к данному фрукту и поставщику:

Как вы уже знаете, эту задачу легко решить с помощью обычной формулы СУММЕСЛИМН:
= СУММЕСЛИМН (C2: C9, A2: A9, «яблоки», B2: B9, «Пит»)

А теперь давайте посмотрим, как эту же задачу можно решить в ранних версиях Excel без СУММЕСЛИМН.Во-первых, вы записываете все условия, которые должны быть выполнены, в форме range = "condition". В этом примере у нас есть две пары диапазон / условие:

Условие 1: A2: A9 = «яблоки»

Условие 2: B2: B9 = "Пит"

Затем вы пишете формулы СУММ, которые «умножают» все ваши пары диапазон / условие, каждая из которых заключена в скобки. Последний множитель - это диапазон сумм, в нашем случае C2: C9:

.

= СУММ ((A2: A9 = «яблоки») * (B2: B9 = «Пит») * (C2: C9))

Как показано на скриншоте ниже, формула отлично работает в последней версии Excel 2013.

Примечание. При вводе любой формулы массива необходимо нажать Ctrl + Shift + Enter. Как только вы это сделаете, ваша формула будет заключена в {фигурные скобки}, что является визуальным признаком того, что формула массива введена правильно. Если вы попытаетесь ввести фигурные скобки вручную, ваша формула будет преобразована в текстовую строку и работать не будет.

Пример 2. Формулы массива СУММ в современных версиях Excel

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

Предположим, у вас есть два столбца, B и C, и вам нужно подсчитать, во сколько раз столбец C больше столбца B, если значение в столбце C больше или равно 10. На ум сразу приходит решение использовать формула массива СУММ:

= СУММ ((C1: C10> = 10) * (C1: C10> B1: B10))

Не видите практического применения этой формулы? Подумайте об этом по-другому 🙂

Предположим, у вас есть список заказов, как показано на скриншоте ниже, и вы хотите знать, сколько продуктов не было доставлено полностью к заданной дате.В переводе на язык Excel имеем следующие условия:

Условие 1: значение в столбце B (Заказанные товары) больше 0

Условие 2: значение в столбце C (Доставлено) меньше, чем в столбце B

Условие 3: Дата в столбце D (Срок исполнения) меньше 1 ноября 2014 г.

Соединив вместе три пары диапазон / условие, вы получите следующую формулу:

= СУММ ((B2: B10> = 0) * (B2: B10> C2: C10) * (D2: D10

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

Вас также может заинтересовать

.Функция

СУММЕСЛИ - служба поддержки Office

Функцию СУММЕСЛИ можно использовать для суммирования значений в диапазоне, который соответствует указанным критериям. Например, предположим, что в столбце, содержащем числа, вы хотите суммировать только значения, превышающие 5. Можно использовать следующую формулу: = СУММЕСЛИ (B2: B25, "> 5")

Это видео является частью учебного курса «Сложение чисел в Excel».

Советы:

• Если хотите, вы можете применить критерии к одному диапазону и суммировать соответствующие значения в другом диапазоне.Например, формула = СУММЕСЛИ (B2: B5, «Джон», C2: C5) суммирует только значения в диапазоне C2: C5, где соответствующие ячейки в диапазоне B2: B5 равны «Джон».

• Чтобы суммировать ячейки на основе нескольких критериев, см. Функцию СУММЕСЛИМН.

Важно: Функция СУММЕСЛИ возвращает неверные результаты, если вы используете ее для сопоставления строк длиной более 255 символов или строки #VALUE! .

Синтаксис

СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])

Аргументы функции СУММЕСЛИ следующие:

• диапазон Обязательно. Диапазон ячеек, которые вы хотите оценить по критериям. Ячейки в каждом диапазоне должны быть числами или именами, массивами или ссылками, содержащими числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (примеры ниже).

• критерия Обязательно. Критерии в форме числа, выражения, ссылки на ячейку, текста или функции, определяющие, какие ячейки будут добавлены. Могут быть включены подстановочные знаки - вопросительный знак (?) Для соответствия любому одиночному символу, звездочка (*) для соответствия любой последовательности символов. Если вы хотите найти настоящий вопросительный знак или звездочку, введите перед символом тильду ( ~ ).

  Например, критерии могут быть выражены как 32, «> 32», B5, «3?», «Яблоко *», «* ~?» Или СЕГОДНЯ ().

  Важно: Любые текстовые критерии или любые критерии, которые включают логические или математические символы, должны быть заключены в двойные кавычки ( "). Если критерий числовой, двойные кавычки не требуются.

• диапазон_суммы Необязательно. Фактические ячейки, которые нужно добавить, если вы хотите добавить ячейки, отличные от тех, которые указаны в аргументе range .Если аргумент диапазон_суммы опущен, Excel добавляет ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяются критерии).

  
Суммарный_диапазон
должен быть того же размера и формы, что и , диапазон . В противном случае производительность может снизиться, и формула суммирует диапазон ячеек, который начинается с первой ячейки в sum_range , но имеет те же размеры, что и range .Например:

  диапазон	диапазон сумм	Фактически суммированные ячейки
  A1: A5	B1: B5	B1: B5
  A1: A5	B1: K5	B1: B5

Примеры

Пример 1


Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel.Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите Enter. При необходимости вы можете настроить ширину столбца, чтобы увидеть все данные.

Пример 2


Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel.Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите Enter. При необходимости вы можете настроить ширину столбца, чтобы увидеть все данные.

Верх страницы

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новую функцию или улучшение в Excel User Voice.

См. Также

Функция СУММЕСЛИМН добавляет все аргументы, соответствующие нескольким критериям.

Функция СУММК суммирует несколько значений после выполнения математической операции возведения в квадрат для каждого из них.

Функция СЧЁТЕСЛИ считает только значения, соответствующие одному критерию.

Функция СЧЁТЕСЛИМН считает только значения, соответствующие нескольким критериям.

Функция IFS (Office 365, Excel 2016 и более поздние версии)

Обзор формул в Excel

Как избежать неправильных формул

Обнаруживать ошибки в формулах

Математические и триггерные функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Использование СУММЕСЛИ, СЧЁТЕСЛИ и связанных функций для быстрого анализа данных (бесплатная предварительная версия)

.