Добавить Countif к формуле массива (промежуточный итог) в Excel

Question

Я новичок в массиве формул и заметил, что в то время как SUBTOTAL включает в себя множество функций, это не функция COUNTIF (только COUNT а также COUNTA).

Я пытаюсь выяснить, как я могу интегрировать COUNTIF-подобная особенность моей формулы массива.

У меня есть матрица, небольшая часть которой выглядит так:

A   B   C   D   E
48  53  46  64  66
48      66  89
40  38  42  49  44

37  33  35  39  41

Благодаря помощи Tom Sharpe в этом посте я (он) смог усреднить сумму каждой строки в матрице при условии, что у нее есть полные данные (поэтому строки 2 и 4 в приведенном выше примере не будут включены).

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

В текущем примере результатом будет 2, так как строка 1 имеет значения 5/5> 45, а строка 3 имеет значение 1> 45. Строка 5 имеет значения < 45, а строки 2 и 3 имеют частично или полностью отсутствующие данные, соответственно,

Я недавно обнаружил SUMPRODUCT функционировать и думать, что, возможно, SUMPRODUCT(--(A1:E1 >= 45 может быть полезным, но я не уверен, как интегрировать его в элегантный код Тома Шарпа, например,

=AVERAGE(IF(SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1)))=COLUMNS(A1:E1),SUBTOTAL(9,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1))),""))

Помните, я больше не ищу среднее значение: я хочу отфильтровать строки на предмет наличия у них полных данных, и если они есть, я хочу посчитать строки, по крайней мере, с 1 записью> 45.

Answer 1

Попробуйте следующее. Введите в качестве формулы массива.

=COUNT(IF(SUBTOTAL(4,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1)))>45,IF(SUBTOTAL(2,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1)))=COLUMNS(A1:E1),SUBTOTAL(9,OFFSET(A1,ROW(A1:A5)-ROW(A1),0,1,COLUMNS(A1:E1))))))

Данные