Excel - использование нескольких критериев в функции COUNTIFS

Question

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

Я ввожу дату в столбец H (назначенная дата). Существует формула для автозаполнения столбца I (срок оплаты):

=IF(ISBLANK(H6), " ", EDATE(H6,6))

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

Я пытался:

=COUNTIFS(I6:I260,"<TODAY()",J6:J260,"<>"&"""")

но я подозреваю, что он считает формулу автозаполнения в столбце I.

Answer 1

Вы делаете две ошибки:

• Когда вы говорите "<TODAY()" , вы сравниваете значение (в столбце I) со строкой TODAY() .  Так, например, если вы поместите дни недели в столбец I виде строк, то Monday , Thursday , Friday , Saturday и Sunday будут считаться < TODAY() , а Tuesday и Wednesday - нет.  Однако никакие фактические значения даты не будут считаться < TODAY() .

  • Обычно (например, если вы делаете IF(D1<S1,…)), все даты считаются меньше всех строк.  Однако для целей COUNTIF даты не считаются меньшими, большими или равными строкам.  Они просто не учитываются.   Это не представляется документально подтвержденным.
  • Тот факт, что значения в столбце I вычисляются по формуле, не является проблемой.
    
    

  Вы должны сказать "<"&TODAY() чтобы получить значение сегодняшней даты.

• Когда у вас есть две двойные кавычки в строке в строковой константе, они действуют как один символ двойной кавычки.  Это сбивает с толку.  Например, если вы скажете "foo""bar" в формуле, оно получит строковое значение foo"bar .  По тому же правилу, если вы скажете """" в формуле, оно вычисляется как строковое значение " , а когда вы говорите "<>"&"""" , оно оценивается как <>" , поэтому вы сравниваете значения в столбце J до " .

  Чтобы проверить, что значения не являются пустыми, вам нужно сказать "<>"&"" или просто "<>" , что ужасно не интуитивно понятно.

  • Немного более понятный синтаксис is =* , который проверяет, является ли значение ненулевой строкой.  Однако <> будет подсчитывать все ячейки, которые не являются пустыми, в то время как =* не будет подсчитывать ячейки, которые содержат числа или даты.  Поскольку вы не говорите, что у вас есть в столбце J , я не знаю, полезно ли это для вас.

Итак, формула, которую вы хотите

=COUNTIFS(I6:I20,"<"&TODAY(), J6:J20,"<>")

Обратите внимание, что это не формула массива, поэтому она не требует Ctrl+Shift+Enter.

Например:

        

• Строка 6 считается потому, что I6 (11 февраля 2019 г.) меньше, чем сегодня (19 февраля 2019 г.), а J6 (foo) не пусто.
• Строки 7-9 не учитываются, потому что в столбце J они не заполнены.
• Строка 10 не считается, потому что I10 пуст.  (То же самое для строк 15-20.)
• Строки 11 и 12 не учитываются, потому что I11 (25 февраля 2019 г.) и I12 (26 февраля 2019 г.) больше сегодняшней даты.
• Строки 13 и 14, потому что даты в столбце I находятся в будущем, а в столбце J они пустые.

Answer 2

Используйте эту формулу Array (CSE), завершите с Ctrl+Shift+Enter.

{=SUMPRODUCT(COUNTIFS($I$139:$I$145,IF({1;0},"","<"&TODAY()),$J$139:$J$145,""))}

NB

• Функция IF генерирует массив, который разрешает что-то вроде этого, {"";">43510"} .
• При необходимости измените ссылки на ячейки в формуле.