Number of rows that (not) contain a specific value

I prepared two ways to solve this problem. The first uses values in a new column and the second uses array formula. Both have their advantages.

Example

Table in the picture below contains information about the success of students making 3 exams. Value “Yes” means he passed, value “No” means he did not pass. Student should have value “Yes” in each column to pass all exams.

Rows not contain value - example1

How many students pass all of exams? The question is, how to count rows having value “Yes” in column B or C or D.

Solution 1

If we could add values into the table, we will create a new column in which will Excel decide about values. Formula in new column is following:

=IF(OR(B2="no",C2="no",D2 ="no"),"no","yes")

We can use this formula as an alternative (the result is the same):

=IF(AND(B2="yes",C2="yes",D2 ="yes"),"yes","no")

Result:

Rows not contain value - solution1a

Now it remains only to count value “yes” in column E. Formula for that:

=COUNTIF(E2:E7,"yes")

Finish:

Rows not contain value - solution1b

Solution 2

This is the way without auxiliary column. The calculation consists of one array formula.

{=COUNTA(A2:A7)-SUM(IF(MMULT(IF(B2:D7 ="no",1,0),{1;1;1})>0,1,0))}

Result:

Rows not contain value - solution2



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress