Excel COUNTIFS с условием ИЛИ

Question

Я хотел бы посчитать клетки на основе нескольких условий. Два из этих условий относятся к одному столбцу и должны иметь ИЛИ-ред. Я знаю, что могу добавить два COUNTIFS, но я решил, что должен быть лучший способ, которого я еще не нашел.

Пример:
Это мои исходные ячейки (столбцы от A до D)

    A String    B String    C String    YearMonth
    12345A      XYZ         UVW1        201301
    ABCDE       XYZ         UVW1        201302
    BCDEF       XYZ         UVW1        201301
    12345A      XYZ         UVW1        201303
    T2345       XYZ         UVW1        201304
    T2345       XYZ         UVW1        201301
    ABCDE       XYZ         UVW1        201301

... и вот как я хочу мои результаты (столбцы H до M)

    Title  XYZ  201301  201302  201303  201304
    UVW1             1       0       1       1
    UVW2             0       0       0       0
    UVW3             0       0       0       0
    UVW4             0       0       0       0
    UVW5             0       0       0       0
    UVW6             0       0       0       0
    UVW7             0       0       0       0

Жирное число 1 должно быть 2, используя эту формулу:

=SUMPRODUCT(($B:$B=$I$1)*($C:$C=$H2)*($D:$D=J$1)*ISNUMBER(MATCH(MID($A:$A;1;2);{"1?";"T2"};0)))

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

=COUNTIFS($B:$B;$I$1;$C:$C;$H2;$D:$D;J$1;$A:$A;"1*")+COUNTIFS($B:$B;$I$1;$C:$C;$H2;$D:$D;J$1;$A:$A;"T2*")

Есть идеи по рабочей формуле, которая не повторяет условия?

РЕДАКТИРОВАТЬ: Кроме того, как было указано, я мог бы добавить новый столбец для сортировки, но, к сожалению, добавление столбца будет означать прохождение через некоторые почти не поддерживаемые макросы, чего я надеюсь избежать.

Answer 1

Чтобы создать результаты, используйте функцию сводной таблицы, которую предоставляет Excel. Вот эти шаги:

• Выберите ваши исходные данные (столбцы A:D)
• Вставьте сводную таблицу (часто находится на вкладке "Вставка" и / или "Данные" на ленте)
• Установите строку C, чтобы быть вашими заголовками строк
• Установите YearMonth в качестве заголовков столбцов
• Установите строку B в свой фильтр отчетов
• Установите строку (или любое другое поле) в области значений и убедитесь, что элементы подсчитаны (не суммированы)
• Отрегулируйте дальше по своему вкусу.

Чтобы отфильтровать результаты по строке A, у вас есть два варианта:

1. Добавьте в исходную таблицу столбец, который проверяет ваши условия (в вашем случае что-то подобное в строке 2: =OR(LEFT(A2;2)="T2";LEFT(A2;1)="1") и добавьте это поле для фильтра отчетов вашей сводной таблицы и фильтр на TRUE .
2. Добавьте строку A в свой фильтр отчетов и вручную выберите каждое значение, которое вы хотите показать. Обратите внимание, что это предполагает, что количество значений относительно ограничено, потому что в противном случае это будет довольно трудоемкой задачей для поддержания правильной фильтрации.

Лично мне больше нравится первый из-за лучшей прозрачности и ремонтопригодности.

Answer 2

Хитрость заключается в том, чтобы понять, что соответствует 1? или T2 можно рассматривать как два отдельных критерия, но они должны сочетаться с + вместо *. Поскольку они являются взаимоисключающими, их сумма всегда будет 0 или 1.

=SUM(($B$2:$B$8=$I$1)*($C$2:$C$8=$H3)*($D$2:$D$8=K$1)*
     ((LEFT($A$2:$A$8,1)="1")+(LEFT($A$2:$A$8,2)="T2")))

Начав с опции SUMPRODUCT, я впервые заметил, что у нее только один аргумент, поэтому я переключился на использование только SUM. Я также перешел от обработки всего столбца к необходимому диапазону, потому что в моей системе с недостаточным количеством сил пересчеты занимали так много времени. Если вы заботитесь о скорости, может быть важно избежать обработки всего столбца, но у вас, вероятно, больше опыта с этим, чем у меня.