Формулы Excel - подсчет строк, соответствующих двум критериям, в двух столбцах в строке

Подсчет строк в таблице, если они соответствуют двум критериям в двух столбцах в строке

Синтаксис используемых функций

 COUNTIFS (критерии_ранж1, критерии1, [критерий_2, критерии2]…)

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

объяснение

Для подсчета количества строк, в которых две циртерии совпадают, можно использовать функцию COUNTIFS. В этом примере мы хотим посчитать, что класс IX, а оценки в 1-м сем. - более 70.

формула

=COUNTIFS(C6:C11,"IX",D6:D11,">70")

Как работает эта формула

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

Подсчитайте строки, соответствующие двум критериям в двух столбцах в строке с помощью SUMPROUDCT ()

Синтаксис используемых функций

 SUMPRODUCT (массив1, [массив2], [массив3], ...)

Функция SUMPRODUCT используется для умножения соответствующих компонентов в данных массивах и возвращает сумму этих продуктов.

Что делать?

Данная таблица содержит имя, класс, оценку 1-го и 2-го семистров некоторых студентов. Количество строк будет сгенерировано, когда в одной строке класс IX, а оценка 1-го сема больше 70.
Использование функции SUMPRODUCT является хорошим способом решения этой проблемы, поскольку функция SUMPRODUCT может обрабатывать операции с массивами.

формула

=SUMPRODUCT((Class="IX")*(_1st_Sem>70))

Как работает эта формула

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

=SUMPRODUCT(({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;})*({FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;}))

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

=SUMPRODUCT({0;0;0;1;0;1;})

И, наконец, только с одним массивом SUMPRODUCT просто складывает элементы в массив и возвращает сумму.

Предыдущий: Excel Formulas - Подсчитать количество строк для определенного совпадающего значения
Далее: Excel Formulas - Подсчитать количество строк, содержащих несколько условий ИЛИ