Суммирование по условию с помощью SUMIF

Обычная SUM складывает каждое число в диапазоне. Но часто нужно сложить только некоторые из них. Представьте таблицу продаж, где в столбце A указан регион, а в столбце B — сумма. Вам не нужен итог по всем продажам — нужен только итог по региону Восток.

Это условная сумма, и предназначенная для неё функция — SUMIF. Она складывает числа только при выполнении соответствующего условия, автоматически пропуская остальные.

SUMIF принимает по порядку три элемента:

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

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

Предположим, названия регионов находятся в диапазоне A2:A10, а суммы продаж — в диапазоне B2:B10. Чтобы получить итог только для региона Восток, проверьте столбец A на наличие слова «Восток» и сложите соответствующие суммы из столбца B.

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

Если условие представляет собой слово, заключите его в двойные кавычки: "East". Без кавычек таблица решит, что Восток — это имя или метка, и, скорее всего, вернёт ноль.

Сопоставление не зависит от регистра, поэтому "east" и "East" соответствуют одним и тем же строкам. Просто убедитесь, что написание точно совпадает с данными и в нём нет лишних пробелов.

Указать условие вручную вполне можно, но удобнее поместить его в ячейку. Если в D1 содержится слово «Восток», используйте ссылку на D1 вместо записи текста.

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

Критерии не обязательно должны быть текстовыми. Чтобы сложить все суммы, точно равные 500, просто используйте это число в качестве условия.

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

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

• ">100" больше 100
• "<=50" 50 или меньше
• "<>0" не равно нулю

Эта формула складывает все продажи, превышающие 100. И оператор, и число находятся внутри одной пары кавычек.

Что делать, если пороговое значение находится в ячейке — например, в D1 указано 100 — и нужно сложить суммы, превышающие его? Нельзя записать ">D1", потому что это станет буквальным текстом D1. Вместо этого объедините оператор и ячейку с помощью амперсанда.

Амперсанд присоединяет текст ">" к значению внутри D1 и на лету создаёт критерий ">100".

Диапазон и диапазон суммирования должны иметь одинаковую высоту и начинаться с одной строки. Если названия регионов находятся в A2:A10, суммы должны находиться в B2:B10, а не в B3:B11.

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

Представьте таблицу расходов: в столбце A указаны категории, а в столбце B — затраты. Вы хотите создать небольшую итоговую таблицу с суммами для категорий «Путешествия», «Еда» и «Офис».

Поместите названия категорий в ячейки D2, D3 и D4, а затем запишите одну функцию SUMIF со ссылкой на ячейку с названием. Протяните формулу вниз, и каждая строка подсчитает итог для своей категории. Один шаблон формулы — три мгновенных промежуточных итога.

Обратите внимание: в последнем примере использовались A:A и B:B — целые столбцы. Это удобно, если строки постоянно добавляются, поскольку Вам никогда не придётся расширять диапазон.

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

Проверьте, насколько хорошо Вы понимаете порядок аргументов SUMIF.

Теперь Вы умеете складывать числа по условию с помощью SUMIF. Главное:

• Порядок такой: диапазон, критерий, диапазон суммирования.
• Текстовые критерии нужно заключать в кавычки, а сопоставление не зависит от регистра.
• Операторы, например ">100", находятся внутри кавычек; чтобы объединить оператор с ячейкой, используйте &.
• Диапазон и диапазон суммирования должны охватывать одни и те же строки.

Далее Вы научитесь считать совпадающие строки с помощью COUNTIF.