CountIF по диапазону критериев

У меня есть следующие столбцы данных:

Pool Amount|Calculated Payment| 10% | 15% | 20% | 30% | 40% | 50%

Сумма пула и Расчетный платеж являются статическими полями валюты, все процентные поля содержат варианты этой формулы:

e.g. C2 =IF(B2<(A2*0.9),A2*0.9,IF(B2>(A2*1.1),A2*1.1,B2))

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

Для одной ячейки в поле 10% я бы использовал:

Мин. =COUNTIF(C2,A2*0.9) и макс. =COUNTIF(C2,A2*1.1)

Затем я мог бы создать два столбца из 0 и 1 и SUM их, но это кажется немного неуклюжим. Есть ли вариант следующей формулы, которую я мог бы использовать:

=COUNTIF(C2:C1162, A2:A1162*0.9) чтобы показать общее количество в одной ячейке?

Это потенциально работа для сводной таблицы с пользовательской функцией?


excel pivot-table
person Alistair Weir    schedule 10.07.2012    source источник
comment
Похоже, вам нужно начать вникать в формулы массива   -  person lc.    schedule 10.07.2012

Ответы (2)


arrow_upward
1
arrow_downward

СЧЁТЕСЛИ нельзя использовать для сравнения каждого элемента одного диапазона/массива с каждым элементом другого, но вы можете использовать СУММПРОИЗВ, чтобы сделать это и избежать формул, которые требуют «ввода массива», т.е. эта формула для подсчета минут в поле 10%

=SUMPRODUCT((B2:B1162< A2:A1162*0.9)+0)

и для Макса

=SUMPRODUCT((B2:B1162> A2:A1162*1.1)+0)

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

person barry houdini    schedule 10.07.2012
comment
+0 дает вам 1 и нули, а не ИСТИНА / ЛОЖЬ, чтобы СУММПРОИЗВ или СУММ могли их обработать, вы можете использовать это, что, возможно, более понятно =SUM(IF(B2:B1162> A2:A1162*1.1,1,0)), но опять же это потребует ввода массива - person barry houdini; 10.07.2012

arrow_upward
1
arrow_downward

Я считаю, что вы можете добиться желаемого с помощью формула массива. Ваша формула для «минимальной ячейки 10%» должна быть правильной, просто нажмите CTRL+SHIFT+ENTER при вводе формулы, чтобы сделать это формула массива.

Я считаю, что вам нужно использовать SUM вместо COUNTIF в формуле массива (чтобы суммировать все TRUE как 1 и FALSE как 0). Прошло некоторое время с тех пор, как я использовал один.

person lc.    schedule 10.07.2012
comment
Спасибо, изучу формулы массива, никогда раньше их не использовал. - person Alistair Weir; 10.07.2012