Как добавить динамический расчет в качестве критерия?

Допустим, в столбце A у меня есть список дат начала, а в столбце B — список дат окончания. Я хочу построить одну формулу (без добавления дополнительного столбца), которая подсчитывает, сколько раз дата окончания превышает 120 дней с даты начала.

Я попробовал следующую формулу в C1:

=COUNTIF(B:B,A:A-120)

но это не работает, так как второй атрибут формулы СЧЁТЕСЛИ является критерием, похоже, он не принимает диапазон. Он обрабатывает A:A как A1, потому что я поместил свою формулу в C1.

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

ОБНОВЛЕНИЕ: в некоторых строках написано "NULL", когда нет доступных дат, и поэтому это не работает с =SUMPRODUCT(--(A:A<B:B-120))


excel formula range countif criteria
person Faust    schedule 08.05.2014    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Вместо этого вы можете использовать SUMPRODUCT, где вы можете использовать диапазоны:

=SUMPRODUCT(--(A:A<B:B-120))

Если A:A меньше, чем B:B после удаления 120 дней, то (A:A<B:B-120) вернет true для каждой строки, где это применимо, и false в противном случае.

-- в начале преобразует их в 1 и 0 соответственно, а SUMPRODUCT добавит их.

Обратите внимание, что формула будет работать быстрее, если вы используете меньший диапазон, например. A1:A100 и B1:B100 (или любой другой диапазон, который вы считаете).

Попробуйте использовать это, если в вашем диапазоне также есть недаты:

=SUM(IFERROR(A:A<B:B-120,0)*1)

И нажмите Ctrl+Shift+Enter после ввода формулы для вызова массива (вы можете использовать SUMPRODUCT, но для этого потребуется вызов массива так что вы можете просто использовать SUM) .

person Jerry    schedule 08.05.2014
comment
Сначала я не понял --. Потом я подумал, почему он не использовал 1* и стало ясно. - person stenci; 09.05.2014
comment
@stenci Это то же самое, что использовать *1 или +0 :) Спасибо, @pnuts! - person Jerry; 09.05.2014
comment
Это хорошо работает, если в каждой строке есть даты. Однако некоторые строки не имеют даты и показывают только NULL как в A, так и в B. Поэтому возвращается ошибка. Есть ли способ исправить это? - person Faust; 09.05.2014
comment
После дальнейшего тестирования =SUMPRODUCT(--(A:A<B:B-120)*ISNUMBER(B:B)*ISNUMBER(A:A)) все еще не решает. Поскольку мы применяем расчет (B:B-120), он возвращает ошибку. Есть ли способ исправить это? Я пробовал =SUMPRODUCT(--(A:A<N(B:B-120))*--(ISNUMBER(B:B))*--(ISNUMBER(A:A))), но это тоже не сработало. - person Faust; 09.05.2014
comment
@Faust Хорошо, я думал раньше, что с этой проверкой ошибка исчезнет. Сейчас я попробовал несколько вещей и отредактировал свой ответ для рабочей формулы. - person Jerry; 09.05.2014