image

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

Сценарий:

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

link: / summing-excel-sumproduct-function [SUMPRODUCT function] — универсальная функция, которую можно использовать для подсчета с критериями, иногда даже лучше, чем функция counttif.

Как решить проблему?

Для этой статьи нам потребуется использовать link: / summing-excel-sumproduct-function [SUMPRODUCT function]. Теперь составим формулу из этих функций. Здесь нам даны данные двух диапазонов, и нам нужно подсчитать значения, в которых формула удовлетворяет критериям.

_ Общая формула: _

= SUMPRODUCT ( -- ( range1 operator range2 ) )

диапазон1: 1-й диапазон диапазон2: оператор 2-го диапазона: критерии, условие задано как оператор между двумя диапазонами

Пример:

Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже.

Это рекорд последних 7 крупных матчей фан-клубов Премьер-лиги.

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

image

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

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

Используйте формулу:

= SUMPRODUCT ( -- ( E5:E11 > F5:F11 ) )

E5: E11: 1-й диапазон, забитые голы на своем поле F5: F11: 2-й диапазон, забитые голы соперником>: критерии, условие задано как большее, чем оператор Объяснение:

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

  • E5: E11 (диапазон1) должен быть больше, чем F5: F11 (диапазон2).

    • * оператор, используемый для преобразования значения ИСТИНА в 1 и значение Ложь в 0.

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

image

Здесь диапазон указан как ссылка на ячейку. Нажмите Enter, чтобы получить счет.

image

Как видите, всего матчей, в которых хозяева поля имели преимущество и выиграли, составляет 4 матча.

Приведенный выше пример был легким. Чтобы было интересно, посчитаем, сколько матчей выиграла команда во всех данных.

image

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

Теперь мы будем использовать следующую формулу, чтобы подсчитать количество игр, Человек.

«Юнайтед» выиграл.

Используйте формулу:

= SUMPRODUCT ( -- ( C5:C11 = G5 )  ( E5:E11 > F5:F11 ) + ( D5:D11 = G5 )  ( E5:E11 < F5:F11 ) )

Пояснение:

(C5: C11 = G5) (E5: E11> F5: F11) проверяет команду хозяев как Man.

Объединились и забили больше голов, чем команда соперника.

(D5: D11 = G5) (E5: E11 <F5: F11) проверяет команду как Man.

Объединены и забили больше голов, чем хозяева поля.

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

image

Здесь диапазон указан как ссылка на ячейку. Нажмите Enter, чтобы получить счет.

image

Как видите общее количество матчей, в которых Man. Объединенная команда выиграла 2 матча.

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

Примечания:

  1. Формула работает только с числами.

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

  3. Функция СУММПРОИЗВ рассматривает логическое значение ИСТИНА как 1 и Ложь как 0.

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

Похожие статьи

link: / summing-excel-sumproduct-function [Как использовать функцию СУММПРОИЗВ в Excel]: возвращает СУММ после умножения значений в нескольких массивах в Excel.

link: / counting-countifs-with-dynamic-conditions-range [COUNTIFS с динамическим диапазоном критериев]: подсчет ячеек в зависимости от других значений ячеек в Excel.

link: / counting-countifs-two-criterion-match [COUNTIFS Two Criteria Matches:] Подсчет ячеек, соответствующих двум различным критериям в списке в excel.

link: / counting-countifs-with-or-for-множественные критерии [СЧЁТЕСЛИ С ИЛИ для нескольких критериев]: Подсчет ячеек, которые имеют несколько критериев, совпадающих с использованием функции ИЛИ.

link: / counting-the-countifs-function-n-excel [Функция COUNTIFS в Excel]: подсчет ячеек в зависимости от других значений ячеек.

link: / tips-how-to-use-countif-in-vba-in-microsoft-excel [Как использовать Countif в VBA в Microsoft Excel]: Подсчет ячеек с помощью кода Visual Basic для приложений.

link: / tips-excel-wildcards [Как использовать подстановочные знаки в Excel]: Подсчет ячеек, соответствующих фразам, с использованием подстановочных знаков в excel

Популярные статьи

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-повышения-продуктивность [50 сочетаний клавиш Excel для повышения производительности]

link: / excel-generals-how-to-edit-a-dropdown-list-in-microsoft-excel [Редактировать раскрывающийся список]

link: / excel-range-name-absolute-reference-in-excel [Абсолютная ссылка в Excel]

link: / tips-conditional-formatting-with-if-statement [Если с условным форматированием]

link: / logic-formulas-if-function-with-wildcards [Если с wildcards]

link: / lookup-formulas-vlookup-by-date-in-excel [Vlookup-by-date]

link: / tips-inch-to-ft [Преобразование дюймов в футы и дюймы в Excel 2016]

link: / excel-text-edit-and-format-join-first-and-last-name-in-excel [Соединить имя и фамилию в excel]

link: / counting-count-cells-which-match -ither-a-or-b [Подсчет ячеек, соответствующих A или B]