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

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

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

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

У нас есть список сотрудников, выбирая город нам необходимо посчитать сумму подключенных услуг по сотрудникам и видам услуг. То есть нам необходимо заполнить вот такую таблицу (то что выделено желтым).

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

Для наглядности я перенес данную таблицу на один лист с исходными данными.

Синтаксис функции СУММЕСЛИМН:

СУММЕСЛИМН( диапазон_суммирования ; диапазон_условий1 ; условия1 ;;…) диапазон_суммирования — В нашем случае нам необходимо просуммировать количество подключенных услуг, поэтому это столбец Количество и диапазон Е2:E646

Далее указываются условия по которым необходимо просуммировать услуги. У нас три условия:

1. должна совпадать фамилия сотрудника;
2. должна совпадать услуга;
3. должен совпадать город.

диапазон_условий1 — первое условие у нас сотрудники и диапазон условий это столбец с именами ФИО сотрудников A2:A646

условия1 — это сам сотрудник, так как мы начинаем прописывать формулу напротив сотрудника Апанасенко Е.П то и условия1 у нас будет ссылка на его ячейку G3

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

Продолжим, следующая условие это услуга

диапазон_условий2 — это столбец с услугами D2:D646

условия2 — это ссылка на услугу 1, то есть H2

Вот как должна выглядеть наша формула на текущий момент:

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2

Добавляем третье условие по городам

диапазон_условий3 — диапазон условий по городам это столбец «Город клиента» и диапазон B2:B646

условия3 — это ссылка на город в раскрывающемся списке G1

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

=СУММЕСЛИМН( E2:E646 ; A2:A646 ; G3 ; D2:D646 ; H2; B2:B646; G1 )

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

Во-первых все диапазоны условий у нас не двигаются и постоянны поэтому закрепим их с помощью знака доллара (выделить данный диапазон в формуле и нажать клавишу F4):

Диапазон суммирования у нас так же постоянный E2:E646 → $E$2:$E$646

Так же условия3 по городу G1 y нас всегда находится только в ячейке G1 и не должен смещаться при протягивании, поэтому так же закрепляем данную ячейку

G1 → $G$1

Услуги (условия2) при протягивании вправо должны меняться по столбцам, а вот строка при протягивании вниз не должна меняться, поэтому закрепляем только строку

H2 → H$2

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

G3 → $G3

Итоговая формула будет выглядеть следующим образом

=СУММЕСЛИМН( $E$2:$E$646 ; $A$2:$A$646 ; $G3 ; $D$2:$D$646 ; H$2; $B$2:$B$646 ;$ G$1 )

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

Суммирование значений по заданным аргументам при помощи функций ВПР и СУММ

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

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

Таблица данных — ежемесячные продажи:

Эта задача решается через использование в функции ВПР() массива в третьем аргументе (номер_столбца).

=СУММ (ВПР (искомое значение, интервальный просмотр, {2,3,4}, ЛОЖЬ)).

Исходя из примера выше, в массиве {2, 3, 4} рассматриваемого аргумента для суммирования чисел из столбцов 2,3 и 4 используются функции ВПР().

Для получения искомого числа из столбцов от B до M, сочетание функций ВПР() и СУММ() выглядит так:

=СУММ (ВПР (B2, ‘Monthly sales’! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, ЛОЖЬ))

{=СУММ (ВПР(B2, ‘Monthly sales’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, ЛОЖЬ))}

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

СОВЕТ. Искомое значение может указываться при помощи ссылки на столбец , как показано на рисунке 2. Поскольку рассматриваемая в примере таблица создана посредством инструмента «Вставка” → «Таблица», она стала полнофункциональной. В такой таблице внесенные изменения по умолчанию дублируются на выбранный столбец в вышестоящую ячейку.Исходя из указанной информации, можно сделать вывод о том, что совмещать функции ВПР() и СУММ() в работе достаточно просто. Хотя предложенный способ – не единственно верный путь, в частности, при работе с таблицами с большим количеством данных. В связи с тем, что каждый аргумент делает запрос к функции ВПР(), применение большого количества формул массива отрицательно сказывается на скорости работы книги Excel. Отсюда вывод: большое количество формул в книге и аргументов в массиве может существенно замедлить работу Excel.

Применение сочетания функций ПОИСКПОЗ() и ИНДЕКС() вместо ВПР() и СУММ() поможет обойти эту проблему. Использование предложенных формул будет изучаться в другой обучающей статье.

Функция СУММПРОИЗВ – подробное описание

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

Сами способы следующие:

1. Определение суммы произведений массива.
2. Проверка значения на предмет соответствия определенным критериям, получение их произведений и суммирования.
3. Использование в качестве замены формулы массива.

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

Синтаксис функции СУММПРОИЗВ

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

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

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

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

1

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

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

2

Функция СУММПРОИЗВ с условием

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

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

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

Чтобы выполнить эту задачу с помощью этой функции, необходимо ее использовать и записать два условия в самом начале. Каждое из условий заключается в скобках. Поскольку в нашем случае должно быть соответствие сразу двум критериям, то мы использовали символ звездочки (*). Синтаксис виден на этом скриншоте. Вам достаточно просто подставить соответствующие вашему случаю значения в необходимые места. И дело сделано!

3

Давайте рассмотрим аргументы более подробно:

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

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

4

Пример суммирования с использованием функции СУММЕСЛИ

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

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

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

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

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

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

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Затем прописывается формула для первой группы и протягивается на все остальные

Здесь только нужно обратить внимание на относительность ссылок. Диапазон с критериями и диапазон суммирования должны быть абсолютным ссылками, чтобы при протягивании формулы они не «поехали вниз», а сам критерий, во-первых нужно указать мышкой (а не прописать вручную), во-вторых, должен быть относительной ссылкой, так как каждая сумма имеет свой критерий суммирования

Заполненные поля Мастера функций при подобном расчете будут выглядеть примерно так.

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.

3-D сумма, или работаем с несколькими листами рабочей книги Excel.

Чтобы подсчитать цифры из одинаковой формы диапазона на нескольких листах, вы можете записывать координаты данных специальным синтаксисом, называемым «3d-ссылка».

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

Посмотрите на этом небольшом видео, как применяются 3-D формулы.

Как видите, у нас имеется 4 одинаковых таблицы. Стандартный метод нахождения выручки за месяц –

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

А вот 3D-метод:

Мы указываем программе, что нужно произвести вычисления с ячейками B2:B8, находящимися на листах неделя1, неделя2, неделя3, неделя4. Здесь номер листа последовательно увеличивается на 1.

Важное замечание. Вы можете пользоваться 3D-ссылками и в других случаях

К примеру, выражение

найдет среднее значение однодневной выручки за месяц.

Суммирование ячеек по условию

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

• Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
• Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как . Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие ;
• Диапазон суммирования – массив значений, которые нужно сложить.

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

Синтаксис функции и принцип работы

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

Синтаксис СУММЕСЛИ выглядит следующим образом:

=СУММЕСЛИ(диапазон; критерий; ), где:

• диапазон – список значений, к которым будет применено ограничение;
• критерий – конкретное условие;
• диапазон суммирования – список значений, которые будут суммироваться.

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

Как суммировать весь столбец либо строку.

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

Если поставить формулу суммы в G2

то получим общую выручку по первому отделу.

Обратите внимание, что наличие текста, а не числа, в ячейке B1 никак не сказалось на подсчетах. Складываются только числовые значения, а символьные – игнорируются

Все сказанное выше в полной мере относится и к работе со строками.

Но суммирование столбца целиком встречается достаточно редко. Гораздо чаще область, с которой мы будем работать, нужно указывать более тонко и точно.

Примеры использования

Рассмотрим все возможные ситуации применения одноименной функции.

Общий вид

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

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

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

• C3:C12 – тип одежды;
• «одежда» – критерий;
• F3:F12 – интервал суммирования.

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

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

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

• F3:F12 – диапазон суммирования;
• «одежда» и 50 – критерии;
• C3:C12 и E3:E12 – диапазоны типов товара и стоимости единицы соответственно.

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

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

Для вставки новой строки нужно нажать ПКМ на интересующей ячейке и выбрать «Вставить» – «Строку».

Далее просто введите новые данные или скопируйте их с другого места. Итоговое значение соответственно изменится. Аналогичные трансформации происходят и при редактировании или удалении строк.

На этом я заканчиваю. Вы увидели основные примеры использования функции СУММЕСЛИ в Excel. Если есть какие-то рекомендации или вопросы – милости прошу в комментарии.

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

Посчитать сумму с помощью выделения области ячеек

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

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

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

Как посчитать сумму в столбце с помощью формулы простого сложения

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

• кликните левой клавишей мыши на ячейку, в которой вы хотите получить результат сложения;
• введите формулу:

=B2+B3+B4

В формуле выше указывайте номера ячеек которые вы хотите суммировать:

Как посчитать сумму в столбце с помощью кнопки “Автосумма”

Если вы хотите посчитать сумму чисел в столбце и оставить этот результат в таблице, то, пожалуй, самый простой способ – это использовать функцию “Автосумма”. Она автоматически определит необходимый для суммирования диапазон ячеек и сохранит результат в таблице.

Для подсчета чисел в столбце с помощью автосуммы сделайте следующее:

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

на панели инструментов выберите значок “Автосумма”:

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

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

Как посчитать сумму в столбце с помощью функции СУММ в Excel

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

выделите левой клавишей мыши ячейку и введите функцию “СУММ“, задав необходимый диапазон ячеек:

нажмите кнопку “Enter” и функция посчитает сумму в указанном диапазоне.

Как посчитать сумму в столбце в Excel с помощью таблицы

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

выделите диапазон ячеек с данными и преобразуйте их в таблицу с помощью клавиши на панели инструментов “Форматировать как таблицу”:

после того как ваши данные представлены в формате таблицы, на вкладке “Конструктор” на панели инструментов выберите пункт “Строка итогов” для того, чтобы добавить сумму колонок под таблицей:

Как посчитать сумму в нескольких столбцах в Экселе одновременно

Для того чтобы подсчитать сумму значений по нескольким колонкам одновременно – выполните следующие шаги:

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

нажмите на панели инструментов кнопку “Автосумма”:

после этого действия, система автоматически посчитает сумму по выделенным колонкам и строкам:

Как вычислить сумму каждой N-ой строки.

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

Иначе говоря, нужно, двигаясь вниз, взять каждую третью строчку.

Это можно сделать двумя способами.

Первый – самый простой, «в лоб». Складываем все цифры нужного отдела обычной математической операцией сложения. Выглядит просто, но представьте, если у вас статистика, предположим, за 3 года? Придется обработать 36 чисел…

Второй способ – для более «продвинутых», но зато универсальный.

Записываем

Как это работает? Нам нужна 1-я, 3-я, 6-я и т.д. позиции. При помощи функции СТРОКА() мы вычисляем номер текущей позиции. И если остаток от деления на 3 будет равен нулю, то значение будет учтено в расчете. В противном случае – нет.

Для такого счетчика мы будем использовать номера строк. Но наше первое число находится во второй строке рабочего листа Эксель. Поскольку надо начинать с первой позиции и потом брать каждую третью, а начинается диапазон со 2-й строчки, то к порядковому номеру её добавляем 1. Тогда у нас счетчик начнет считать с цифры 3. Для этого и служит выражение СТРОКА(C2:C16)+1. Получим 2+1=3, остаток от деления на 3 равен нулю. Так мы возьмем 1-ю, 3-ю, 6-ю и т.д. позиции.

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

Когда будем находить продажи по Отделу 2, то изменим выражение:

Ничего не добавляем, поскольку первое подходящее значение как раз и находится в 3-й позиции.

Аналогично для Отдела 3

Вместо добавления 1 теперь вычитаем 1, чтобы отсчет вновь начался с 3. Теперь брать будем каждую третью позицию, начиная с 4-й.

Ну и, конечно, не забываем нажимать CTRL+SHIFT+ENTER.

Примечание. Точно таким же образом можно суммировать и каждый N-й столбец в таблице. Только вместо функции СТРОКА() нужно будет использовать СТОЛБЕЦ().

Функция СУММЕСЛИМН

​ или применяется к​ с несколькими условиями,​ проблем при использовании​

Синтаксис

​ заданных аргументом​Использовать форматы конечных ячеек​

• ​Функция СУММЕСЛИМН — одна из​ условием в числовой​

• ​ решается одной формулой:​Если условий больше одного​

Примеры

​ условиям значения в​	​Продавец​	​ нескольким условиям. Например,​
​ окончанием периода, и​	​ Enter, как обычно,​	​ то функция​
​ 68.​	​ определенном городе с​	​ функция СУММЕСЛИМН, которая​
​Двигаем поле условий​	​ можно, не только,​	​Рассмотрим,​
​(COUNT).​	​=СУММЕСЛИ(A2:A5;»>» &C2;B2:B5)​	​ по размерам с​
​описаны ниже.​	​ правильным порядком аргументов.​	​ диапазоне ячеек, заданном​
​5​	​ с помощью этой​	​ то, в формулу​
​ а​	​СУММЕСЛИ (SUMIF)​	​По такому же принципу​
​ учетом вида услуги.​	​ позволяет при нахождении​	​ полосой прокрутки, пишем​
​ по столбцам и​	​как применять символы в​
​=СЧЁТ(A1:A5)​Сумма комиссионных за имущество,​	​ аргументом​Диапазон​​Одинаковое количество строк и​​ аргументом​Яблоки​​ функции можно найти​​ приходится подставлять его​Ctrl + Shift +​​не поможет, т.к.​​ можно сделать выпадающий​​Как использовать функцию СУММЕСЛИМН​​ суммы учитывать сразу​ следующее условие.​ строкам, но и​ формулах​​=COUNT(A1:A5)​​ стоимость которого превышает​диапазон​   . Обязательный аргумент. Диапазон​​ столбцов для аргументов,​​Диапазон_суммирования​Артем​​ число всех розничных​​ вручную ((((​ Enter​ не умеет проверять​ список для услуг.​ в Excel:​​ несколько значений. В​​В строке «Диапазон​
​ выбирать для сложения​Excel, чтобы посчитать сумму​	​Для подсчета ячеек по​ значение в ячейке​. При определении фактических​ ячеек, оцениваемых на​ задающих диапазоны ячеек​(A2:A9). Результат — 30.​​4​​ продавцов, (1) проживающих​​Подскажите пож-та,​​- тогда Excel​ больше одного критерия.​​Имеем таблицу по продажам,​​Вызываем «Мастер функций». В​ самом названии функции​​ условий3» пишем диапазон​​ данные из таблицы​ по условию, выборочно.​​ одному критерию (например,​​ C2.​ ячеек, подлежащих суммированию,​ соответствие условиям. Ячейки​Аргумент​Проблема​​Яблоки​​ в одном регионе,​

Распространенные неполадки

​как сделать по-человечески?​	​ воспримет ее как​
​ Поэтому начиная с​ например, следующего вида:​	​ категории «Математические» находим​ заложено ее назначение:​ С2:С8.​ по разным условиям.​Символы в формулах​​ больше 9), используйте​​490 000 ₽​
​ в качестве начальной​ в каждом диапазоне​Диапазон_условия​Описание​​Ольга​​ (2) чей доход​заранее спасибо​	​ формулу массива и​ версии Excel 2007​Задача​​ СУММЕСЛИМН. Можно поставить​​ сумма данных, если​В строке «Условие3»​Как установить простое​ помогают выбрать из​ ​ функцию​​Скопируйте образец данных из​​ используется верхняя левая​ должны содержать числа,​должен иметь то​Вместо ожидаемого результата отображается​15​ превышает установленный уровень.​

Рекомендации

​alx74​	​ сам добавит фигурные​
​ в набор функций​	​: просуммировать все заказы,​ в ячейке знак​ совпадает множество условий.​ пишем – «яблоки».​​ условие для подсчета​​ таблицы конкретные данные.​СЧЕТЕСЛИ​ приведенной ниже таблицы​ ​ ячейка аргумента​ имена, массивы или​ же количество строк​ 0 (нуль).​Артишоки​Это видео — часть учебного​: А так не​ скобки. Вводить скобки​ была добавлена функция​ ​ которые менеджер Григорьев реализовал​ «равно» и начать​Аргументы функции СУММЕСЛИМН:​Нажимаем «ОК». Получился такой​ данных, смотрите в​ Как использовать функцию​(COUNTIF).​
​ и вставьте его​диапазон_суммирования​	​ ссылки на числа.​ и столбцов, что​Если выполняется поиск текстовых​Артем​​ курса Усложненные функции​​ работает? По-моему, должно​ с клавиатуры не​СУММЕСЛИМН (SUMIFS)​ для магазина «Копейка».​ вводить название функции.​Диапазон ячеек для нахождения​ ​ результат.​ статье «Функция «ЕСЛИ»​ «СУММЕСЛИ», смотрите в​=СЧЁТЕСЛИ(A1:A5;»>9″)​
​ в ячейку A1​, а затем суммируются​ Пустые ячейки и​	​ и аргумент​​ значений, например имени​​3​ ЕСЛИ.​ сработать, попробуйте.​ надо. Легко сообразить,​​- в ней​​Если бы в нашей​

​ Excel покажет список​

Помогите нам улучшить Excel

​ в Excel».​ статье «Функция «СУММЕСЛИ»​=COUNTIF(A1:A5,»>9″)​ нового листа Excel.​ ячейки части диапазона,​ ячейки, содержащие текстовые​Диапазон_суммирования​

support.office.com>

Суммирование ячеек в Excel по условию

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

Пример использования функции СУММЕСЛИ для сопоставления данных

Функцию СУММЕСЛИ можно использовать для связки данных. Действительно, если просуммировать одно значение, то получится само это значение. Короче, СУММЕСЛИ легко приспособить для связки данных как альтернативу функции ВПР. Зачем использовать СУММЕСЛИ, если существует ВПР? Поясняю. Во-первых, СУММЕСЛИ в отличие от ВПР нечувствительна к формату данных и не выдает ошибку там, где ее меньше всего ждешь; во-вторых, СУММЕСЛИ вместо ошибок из-за отсутствия значений по заданному критерию выдает 0 (нуль), что позволяет без лишних телодвижений подсчитывать итоги диапазона с формулой СУММЕСЛИ. Однако есть и один минус. Если в искомой таблице какой-либо критерий повторится, то соответствующие значения просуммируются, что не всегда есть «подтягивание». Лучше быть настороже. С другой стороны зачастую это и нужно – подтянуть значения в заданное место, а задублированные позиции при этом сложить. Нужно просто знать свойства функции СУММЕСЛИ и использовать согласно инструкции по эксплуатации.

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

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

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

На сегодня все. Всех благ и до новых встреч на statanaliz.info.

Накопительное значение, или нарастающий итог.

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

Посмотрите это небольшое видео, а далее поясним все наши действия.

В ячейке С2 записываем формулу

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

Устанавливаем курсор в правый нижний угол ячейки и «протаскиваем» до С13. В результате наша формула будет скопирована по всему выделению. И при этом начальный адрес B$2 не изменится. К примеру, в С13 это выглядит так: =СУММ(B$2:B13).

Как видите, нарастающий итог то увеличивается, то уменьшается в зависимости от результата прошедшего месяца.