Подсчитывать только те строки, в которых два столбца содержат одно из определенного набора значений

Question

Я пытаюсь создать формулу в Excel 2010 для подсчета строк, в которых столбцы A1:A1005 и D1:D1005 содержат либо Y , N либо N/A Я хочу исключить строки, в которых оба столбца не содержат комбинацию этих трех значений. Так, например:

A   B  C  D
N         N
Y         N
N/A

будет иметь счет 2. Как я могу это сделать?

Самое близкое, что я пришел, это:

COUNTIFS('Analysis'!A1:A1005,“(N,Y,N/A)”,'Analysis'!D1:D1005,“(N,Y,N/A)”)

Answer 1

Вы можете использовать Countif с выражением OR следующим образом:

=SUM(COUNTIFS(A1:A20,{"Y","N","N/A"}))

Это смотрит на один столбец. Из вопроса не ясно, как вы хотите обрабатывать значения в другом столбце. Варианты

сумма подсчитывает, где найдено совпадение в одной строке как A, так и D:

=SUM(COUNTIFS(A1:A20,{"Y","N","N/A"},D1:D20,{"Y","N","N/A"}))

сложить счет в A плюс счет в D:

=SUM(COUNTIFS(A1:A20,{"Y","N","N/A"}),COUNTIFS(D1:D20,{"Y","N","N/A"}))

Тестируйте с небольшой выборкой данных, пока не получите ожидаемый результат.

Изменить: если желаемый результат 2, то используйте эту формулу

=SUMPRODUCT((($A$1:$A$20="y")+($A$1:$A$20="n")+($A$1:$A$20="n/a"))*($D$1:$D$20="y")+($D$1:$D$20="n")+($D$1:$D$20="n/a"))

Answer 2

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

=IF(AND(OR($A2="Y",$A2="N",$A2="N/A"),OR($D2="Y",$D2="N",$D2="N/A")),1,"")

перетащите его на столько строк, сколько вам нужно, а затем суммируйте этот столбец. Я проверил это на вашем примере, и это похоже на работу.