Excel сумма двух диапазонов

Финансы в Excel

Суммирование несвязанных диапазонов

Вложения:

sum_areas.xls

[Суммирование несвязанных диапазонов]

37 kB

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

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

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

На практике решение подобных задач зачастую сводится к написанию «некопируемых» формул, либо требует преобразование итоговых формул после добавления данных (см. лист Пример1, ячейки C10,C11). Этот метод допустим только на небольших объемах данных и нарушает основные принципы оптимизации работы в электронных таблицах.

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

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

Обратите внимание, задача корректности формул итогов при добавлении строк решена за счет добавления пустой служебной строки перед строкой итогов (лист Пример2, строки 6 и 22). Без пустой строки при добавлении нового покупателя вниз массива функция СУММ не подхватывает новые данные автоматически. Служебные строки – это стандартный «безопасный» прием при работе с переменным количеством строк с данными. При желании их можно скрыть стандартными методами интерфейса Excel.

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

• Дополнительное преобразования исходных данных, что зачастую сложно выполнимо на практике (представьте, что в исходном файле покупателей не 3, а 3000).
• Сложность добавления информации – нового покупателя требуется добавлять не в одну, а в три таблицы.

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

Как уже отмечалось выше, формулы в ячейках C10:C11 решают задачу верно, но усложняют работу с моделью. Хочется только обратить внимание на применение функции СУММ с дополнительными вычислениями в ячейке C11.

Остальные формулы в диапазоне D10:E11 также верны и при этом полностью соответствуют требованиям задачи. Рассмотрим их поподробнее.

Итого объем, кг: D10

=SUMIF($B3:$B9;$B$3;D3:D9)

Формула очень простая и очевидная для понимания. Суммирование происходит по условию, закрепленному в ячейке $B$3. Вместо $B$3 можно было написать «объем, кг» непосредственно в функции. Это с одной стороны делает формулу более читаемой, с другой стороны, менее безопасно, так как при замене значения в ячейках заголовков, например с «объем, кг» на «объем, л» функция будет работать неверно.

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

Итого объем, кг: E10

{=SUM(IF(MOD(ROW(E3:E9)-ROW(E2);2)=1;E3:E9))}

Идея формулы – посчитать сумму только в нечетных строках данных. Формула работает с массивом ячеек E3:E9 (признак – фигурные скобки). Нечетные строки здесь определяются как остаток равный 1 при делении порядкового номера строки диапазона на 2. Дополнительно для поддержания целостности номера отсчитываются от заголовка таблицы СТРОКА(E2). Это позволяет переносить данную таблицу целиком на другое место на текущем или другом листе модели.

Формулы обработки массивов применяются через нажатие Ctrl+Shift+Enter

Итого выручка, руб: D11

=SUMPRODUCT((D3:D9)*(D4:D10)*($B3:$B9=$B$3))

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

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

=SUMPRODUCT({400;0,06;500;0,15;600;0,14;0}*{0,06;500;0,15;600;0,14;0;1500}*{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE})

Представим в более понятном виде:

При умножении массивов логическое значение ЛОЖЬ преобразуется в 0, а ИСТИНА в 1.

Итого выручка, руб: E11

{=SUM(IF(MOD(ROW(E3:E9)-ROW(E2);2)=1;(E3:E9)*(E4:E10)))}

Формула очень похожа на формулу вычисления итоговых объемов, находящуюся в ячейке E10 (см.выше), только происходит дополнительное умножение на диапазон со сдвигом вниз (E4:E10) – назовем его условно «диапазон цен», хотя это и не совсем верно по смыслу. При обработке массива, в тот момент когда условная функция определяет что строка содержит объемы, происходит дополнительное умножение на ячейку с тем же порядковым номером в своем массиве, но из диапазона цен. Следует заметить, что при применении в формуле нескольких массивов, они должны быть одинаковой размерности. Из-за этого диапазоны обработки в формулах «заезжают» на строку 10, что вообще-то не очень корректно. В нашем примере это не приводит к ошибкам, но в практической работе рекомендуется избегать таких ситуаций. Простейшим решением здесь может быть добавление еще одной пустой строки перед итогами.

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

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

Примеры формулы для суммы диапазонов с условием отбора в Excel

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

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

В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.

Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки «<0» (меньше чем ноль) используется строка «>0» (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.



Пример логического выражения в формуле для суммы с условием

Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно (<1000) придется использовать знак «меньше или равно» (<=1000) иначе цены со значением ровно 1000 не будут включены в расчеты. Более того в таком случае мы уже не можем использовать в другой формуле «больше или равно» (>=1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:

Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!

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

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

Синтаксис формулы суммирования по условию в Excel

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

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

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

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

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

Создание критериев условий для функции СУММЕСЛИ

Второй аргумент функции называется «Критерий». Данный логический аргумент используется и в других подобных логических функциях: СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. В каждом случаи аргумент заполняется согласно одних и тех же правил составления логических условий. Другими словами, для всех этих функций второй аргумент с критерием условий является логическим выражением возвращающим результат ИСТИНА или ЛОЖЬ. Это значит, что выражение должно содержать оператор сравнения, например: больше (>) меньше (<) равно (=) неравно (<>), больше или равно (>=), меньше или равно (<=). За исключением можно не указывать оператор равно (=), если должно быть проверено точное совпадение значений.

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

Таблица правил составления критериев условий:

Во втором аргументе критериев условий можно использовать разные функции и формулы. Ниже на рисунке изображен список дат и присвоенных им значений. Важно отметить что сегодня на момент написания статьи дата – «03.11.2018». Чтобы суммировать числовые значения только по сегодняшней дате используйте формулу:

Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):

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

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

Суммируются все значения ячеек в диапазоне B2:B16 в соответствии со значениями в ячейках диапазона A2:A16, в которых после третьего символа фрагмент строки «-защита».

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

Хитрости »

11 Июнь 2011              346279 просмотров

Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям

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

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

Всего для СУММЕСЛИ предусмотрено три аргумента: Диапазон, Критерий, Диапазон_Суммирования.
=СУММЕСЛИ(A1:A20000;A1;B1:B20000)
=SUMIF(A1:A20000,A1,B1:B20000)

• Диапазон(A1:A20000) — указывается диапазон с критериями. Т.е. столбец, в котором искать значение, указанное аргументом Критерий.
• Критерий(A1)- значение(текстовое или числовое, а так же дата), которое необходимо найти в Диапазоне. Может содержать символы подстановки «*» и «?». Т.е. указав в качестве Критерия «*масса*» будут просуммированы значения, в которых встречается слово «масса». При этом слово «масса» может либо встречаться в любом месте текста, либо в ячейке может быть только одно это слово. А указав «масса*», будут просуммированы все значения, начинающиеся на «масса». «?» — заменяет лишь один символ, т.е. указав «мас?а» вы сможете просуммировать строки и со значением «масса» и со значением «маска» и т.д.
  Если критерий записан в ячейке и надо все же использовать подстановочные символы, то можно сделать ссылку на эту ячейку добавив нужное. Допустим, надо просуммировать значения, содержащие слово «итог». Слово «итог» записано в ячейке A1, в столбце A при этом могут встречаться различные по написанию значения, содержащие слово «итог»: «итоги за июнь», «итоги за июль», «итоги за март». Формула тогда должна выглядеть так:
  =СУММЕСЛИ(A1:A20000;»*»&A1&»*»;B1:B20000)
  «*»&A1&»*» — знак &(амперсанд) объединяет несколько значений в одно. Т.е. в результате получится «*итог*».
  Чтобы лучше понять принцип работы формул лучше использовать инструмент Вычислить формулу: Как просмотреть этапы вычисления формул
  Все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки (=СУММЕСЛИ(A1:A20000;»итог»;B1:B20000)). Если критерием является число, использовать кавычки не требуется. Если требуется найти непосредственно вопросительный знак или звездочку, необходимо поставить перед ним знак «тильды» (~).
  Про тильду и её особенности можно узнать в этой статье: Как заменить/удалить/найти звездочку?
• Диапазон_Суммирования(B1:B20000)(необязательный аргумент) — указывается диапазон сумм или числовых значений, которые необходимо просуммировать.

Как это работает: функция ищет в Диапазоне значение, указанное аргументом Критерий, и при нахождении совпадения суммирует данные, указанные аргументом Диапазон_Суммирования. Т.е. если у нас в столбце А название отдела, а в столбце В суммы, то указав в качестве критерия «Отдел развития» результатом функции будет сумма всех значений столбца В, напротив которых в столбце А встречается «Отдел развития». Фактически Диапазон_Суммирования может не совпадать по размеру с аргументом Диапазон и ошибки самой функции это не вызовет. Однако при определении ячеек для суммирования, в качестве начальной ячейки для суммирования будет использована верхняя левая ячейка аргумента Диапазон_Суммирования, а затем суммируются ячейки, соответствующие по размеру и форме аргументу Диапазон.

Некоторые особенности
Последний аргумент функции(Диапазон_Суммирования — B1:B20000) является необязательным. А это значит, что его можно не указывать. Если его не указать, то функция просуммирует значения, указанные аргументом Диапазон. Для чего это нужно. Например, Вам необходимо получить сумму только тех чисел, которые больше нуля. В столбце А суммы. Тогда функция будет иметь такой вид:
=СУММЕСЛИ(A1:A20000;»>0″)

Что следует стоит учитывать: диапазон_суммирования и диапазон должны быть равны по количеству строк. Иначе можно получить неверный результат. Оптимально, если это будет выглядеть как в приведенных мной формулах: диапазон и диапазон_суммирования начинаются с одной строки и имеют одинаковое количество строк: A1:A20000; B1:B20000

Ложка дегтя: — СУММЕСЛИ(а так же СУММЕСЛИМН, СЧЁТЕСЛИ и иже с ними) всегда стремятся преобразовать все значения аргументов к типам. Это значит, что если у нас в ячейке записано число 23 — оно будет воспринято как число. Если тоже число будет записано как текст — «23», то функция преобразует его сначала в число, а потом уже будет работать с ним. Т.е. и 23 и «23» у нас будут считаться одинаковым значением. С одной стороны это хорошо, но иногда такое поведение может сыграть злую шутку. Например, у Вас в ячейках столбца А расположены некие номера счетов, длина которых более 15-ти символов и могут иметь ведущие нули:
00034889913131323455
0034889913131323455
00034889913131323477
как видно, первые два числа почти одинаковые, но у первого числа три ведущих нуля спереди, а второго — два. И это разные счета. А третий счет вообще отличается на последние цифры. Но СУММЕСЛИ после преобразования все три этих значения будет считать как число 34889913131323400 и если записать функцию так: =СУММЕСЛИ($A$1:$A$3;A1;$B$1:$B$3), где в столбце В суммы для каждого счета, то она просуммирует значения для всех трех счетов, считая их одинаковыми. Особо обращаю внимание еще на тот факт, что все числа после 15-го знака будут преобразованы в нули. Эти особенности всегда необходимо учитывать при использовании данных функций, чтобы не попасть в неловкую ситуацию, когда результат будет некорректным.

Суммирование по двум и более критериям
Но что делать, когда критериев для суммирования 2 и больше? Допустим, Вам надо просуммировать только те суммы, которые относятся к одному отделу и только за определенную дату. Счастливые обладатели версий офиса 2007 и выше могут воспользоваться функцией СУММЕСЛИМН:
=СУММЕСЛИМН($C$2:$C$50;$A$2:$A$50;$I$3;$B$2:$B$50;$H8)
$C$2:$C$50 — диапазон_суммирования. Первым аргументов указывается диапазон ячеек, содержащих суммы, которые и будут собираться в одну.
$A$2:$A$50, $B$2:$B$50 — Диапазон_критерия. Указывается диапазон ячеек, в которых необходимо искать совпадение по критерию.
$I$3, $H8 — критерий. Здесь, как и в СУММЕСЛИ, допускается указание символов подстановки * и ? и работают они так же.

Особенность указания аргументов: сначала указывается диапазон критерия(они пронумерованы) затем через точку-с-запятой указывается непосредственно значение(критерий), которое в этом диапазоне необходимо найти — $A$2:$A$50;$I$3. И никак иначе. Не стоит пытаться сначала указать все диапазоны, а потом критерии к ним — функция выдаст либо ошибку, либо просуммирует не то, что надо.

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

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

Т.к. СУММЕСЛИМН появилась только в версиях Excel, начиная с 2007, то как же быть в таких случаях несчастным пользователям более ранних версий? Очень просто: использовать другую функцию — СУММПРОИЗВ. Не буду расписывать аргументы, т.к. их много и все они являются массивами значений. Данная функция перемножает массивы, указанные аргументами. Я постараюсь описать общий принцип использования этой функции для суммирования данных по нескольким условиям.
Для решения задачи суммирования по нескольким критериям функция будет выглядеть так:
=СУММПРОИЗВ(($A$2:$A$50=$I$3)*($B$2:$B$50=H5);$C$2:$C$50)
$A$2:$A$50 — диапазон дат. $I$3 — дата критерия, за которую необходимо просуммировать данные.
$B$2:$B$50 — наименования отделов. H5 — наименование отдела, данные по которому необходимо просуммировать.
$C$2:$C$50 — диапазон с суммами.

Разберем логику, т.к. многим она будет совершенно не ясна просто при взгляде на данную функцию. Хотя бы потому, что в справке подобное её применение не описывается. Для большей читабельности уменьшим размеры диапазонов:
=СУММПРОИЗВ(($A$2:$A$5=$I$3)*($B$2:$B$5=H5);$C$2:$C$5)
Итак, выражение ($A$2:$A$5=$I$3) и ($B$2:$B$5=H5) являются логическими и возвращают массивы логических ЛОЖЬ и ИСТИНА. ИСТИНА, если ячейка диапазона $A$2:$A$5 равна значению ячейки $I$3 и ячейка диапазона $B$2:$B$5 равна значению ячейки H5. Т.е. получается у нас следующее:
=СУММПРОИЗВ({ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}*{ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ};$C$2:$C$50)
Как видно, в первом массиве два совпадения условию, а во втором одно. Далее эти два массива перемножаются(за это отвечает знак умножения(*)). При перемножения происходит неявное преобразование массивов ЛОЖЬ и ИСТИНА в числовые константы 0 и 1 соответственно({0;1;1;0}*{0;0;1;0}). Как известно, при умножении на нуль получаем нуль. И в результате получается один массив:
=СУММПРОИЗВ({0;0;1;0};$C$2:$C$50)
Далее происходит уже перемножение массива {0;0;1;0} на массив чисел в диапазоне $C$2:$C$50:
=СУММПРОИЗВ({0;0;1;0};{10;20;30;40})
И как результат получаем 30. Что нам и требовалось — мы получаем лишь ту сумму, которая соответствует критерию. Если сумм, удовлетворяющих критерию будет больше одной, то они будут просуммированы.

Преимущество СУММПРОИЗВ

• Если у аргументов вместо знака умножения указать знак плюс:
  ($A$2:$A$5=$I$3)+($B$2:$B$5=H5)
  то условия будут сравниваться по принципу ИЛИ: т.е. суммироваться итоговые суммы будут в случае, если хотя бы одно условие выполняется: или $A$2:$A$5 равна значению ячейки $I$3 или ячейка диапазона $B$2:$B$5 равна значению ячейки H5.
  В этом преимущество СУММПРОИЗВ перед СУММЕСЛИМН. СУММЕСЛИМН не может суммировать значения по принципу ИЛИ, только по принципу И(все условия должны выполняться).
• СУММПРОИЗВ не приводит значения к типам, а это значит, что значения вроде 00034889913131323455, 0034889913131323455, 00034889913131323477 будут восприняты как есть, без всякого отсечения ведущих нулей и знаков после 15-го.
• СУММПРОИЗВ может работать с закрытыми книгами. Например, из одной книги суммируется данные по таблице другой книги. После того как закроете книгу-источник, СУММЕСЛИ и СУММЕСЛИМН выдадут ошибку #ЗНАЧ!(#VALUE). А СУММПРОИЗВ продолжит работать как ни в чем не бывало.
• если вдруг потребовалось получить не сумму, а количество ячеек, удовлетворяющих критериям — из СУММПРОИЗВ достаточно удалить диапазон ячеек для суммирования:
  =СУММПРОИЗВ(($A$2:$A$50=$I$3)*($B$2:$B$50=H5))

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

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

  Сумма по нескольким критериям (41,5 KiB, 13 649 скачиваний)

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

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика