SUM и COUNTIFS с несколькими массивами?

Я пытаюсь получить количество строк, где выполняются конкретные критерии. Я искал SO для ответа на этот вопрос, но пока не смог найти решение (ПРИМЕЧАНИЕ. Я нашел решения для использования SUM и COUNTIFS с одним массивом, но не с несколькими).

Вот формула, которую я использую:

=SUM(COUNTIFS('Sheet1'!$AA:$AA,"<="&$B1,'Sheet1'!$AE:$AE,"="&"",'Sheet1'!$Q:$Q,"<>"&{"Value1","Value2","Value3","Value4"},'Sheet1'!$S:$S,"<>"&{"ValueA","Value B","ValueC","ValueD","ValueE"})) 

Данные – данные о клиенте. Ячейка B1 – это дата окончания недели. Колонка AA в Листе 1 – это дата открытия случая, а столбец AE – закрытая дата. Столбец Q и S являются критериями фильтрации.

Ожидаемое количество возвратов – это количество случаев, которые были открыты в течение текущей недели или ранее (критерии 1), которые все еще открыты (критерии 2), где категория случаев (столбец Q) не является одним из значений в массиве 1 (критерии 3 ), а тип case не является одним из значений в массиве 2 (критерии 4). Возвращаемый результат выше ожидаемого (это было подтверждено путем ручной фильтрации в наборе данных с использованием этих же критериев).

Я предполагаю, что двойной подсчет продолжается, так как формула возвращает 1,828 случаев в течение одной недели, но проверка с проверкой вручную показывает 1,241 (ожидаемый результат). Любые мысли или идеи по этому поводу очень ценятся.

Проблема состоит в том, что подсчет массива против Value1, Value2, Value3, Value4 рассматривается как условие OR, а не условие AND. Даже если Q2 имеет Value1 в нем, он не имеет Value2, Value3 или Value4, поэтому счет возвращается как истинный. Вы должны убедиться, что Q2 не имеет значений Value1, Value2, Value3, Value4 . То же самое касается столбцов S и ValueA, ValueB, ValueC, ValueD . Это поведение OR хорошо работает, когда вы пытаетесь увидеть значения, которые включены, но не тогда, когда они предназначены для исключения.

COUNTIFS с отключением ARRAY

Старомодная функция SUMPRODUCT может обрабатывать условия OR.

COUNTIFS с диапазонами исключения ARRAY

Формула с константами массива в B2 есть,

 =SUMPRODUCT((Sheet1!$AA$2:$AA$999<=$B1)*SIGN(Sheet1!$AA$2:$AA$999)*(Sheet1!$AE$2:$AE$999="")*ISERROR(MATCH(Sheet1!$Q$2:$Q$999, {"Value1","Value2","Value3","Value4"}, 0))*ISERROR(MATCH(Sheet1!$S$2:$S$999, {"ValueA","Value B","ValueC","ValueD","ValueE"}, 0))) 

Альтернативная формула в B3, использующая значения из диапазонов в D4: D7 и E4: E8,

 =SUMPRODUCT((Sheet1!$AA$2:$AA$999<=$B1)*SIGN(Sheet1!$AA$2:$AA$999)*(Sheet1!$AE$2:$AE$999="")*ISERROR(MATCH(Sheet1!$Q$2:$Q$999, $D$4:$D$7, 0))*ISERROR(MATCH(Sheet1!$S$2:$S$999, $E$4:$E$8, 0))) 
  • Передача переменной с апострофом в SQL-запрос
  • Ошибка автозаполнения нескольких листов 1004
  • Разделение двух рядов данных с одинаковыми значениями x в графе рассеяния Excel
  • Вставка одной и той же ячейки в разные строки при замене значения
  • Сравнение уникальных строк данных Excel по рабочим листам
  • Есть ли способ заставить Excel 2013 открывать файлы в одном экземпляре окна?
  • Excel 2013 сводные иерархические, нечетные данные
  • Сохранить файл в другом расширении в Excel 2013
  • формула Итого 109 при скрытии столбцов не работает в excel
  • Как выполнить сравнение таблиц Excel 2013?
  • EXCEL, если ячейка имеет значение, перемещает строку из таблицы в другую таблицу
  • Interesting Posts

    XSSF (POI) – изменение шрифта на сводной таблице

    Производительность вычислений VBA

    используя openpyxl для изменения документа excel без удаления существующих данных и графиков

    Пользовательская функция Excel не пересчитывается в нескольких рабочих листах

    Загрузите книгу Excel 2013 на веб-сайт, размещенный на Azure

    Что такое эквивалент Python бета-версии Excel?

    Почему многострочные ячейки в моем файле CSV появляются с вопросительным знаком в конце каждой строки в Excel?

    Полоса прокрутки ActiveX Slow

    OpenXmlElement, как узнать его значение, – Date / DateTime

    Ячейка гиперссылки к исходному листу

    VBA Sort в Excel оставляет выбранное содержимое таблицы

    Совокупные значения столбцов, такие как группа по запросу с использованием формулы excel

    Мои данные отображаются некорректно в VisualBasic, импортируя их из другого документа

    макрос для скрыть строки в excel 2010

    Сортировка инвентаря Excel VBA

    Давайте будем гением компьютера.