Lesson 203: Count by OR condition in Excel

Conditional counting in Excel can be complicated. But I will guide you in this article.

Count by OR condition in Excel

1. For example, we want to count the number of cells containing Google or Facebook (one column).

2 a. However, if we want to count the number of rows that contain Google or Stanford (two columns), we cannot simply use the COUNTIF function twice (see image below). Rows containing Google and Stanford are counted twice, but we want to count once and return 4.

2b. What we need is an array formula. We use the IF function to check if Google or Stanford has a problem.

Explanation: TRUE = 1, FALSE = 0. For row 1, the IF function gives the values ​​IF (TRUE + TRUE, 1.0), IF (2,1,0), 1. So the first row will be counted. For row 2, the IF function becomes IF (FALSE + FALSE, 1.0), IF (0,1,0), 0. So the second row will not be counted. For row 3, the IF function becomes IF (FALSE + TRUE, 1.0), IF (1,1,0), 1. So the third row will be calculated,…

2 C. All we need is a SUM function that counts these 1s. I will add the SUM function and replace A1 with A1:A8 and B1 with B1:B8.

2d. Finish by pressing CTRL + SHIFT + ENTER.

Note: The formula bar indicates this is an array formula by enclosing it in curly braces {}. Don’t type these yourself. They will disappear when you edit the formula.

Explanation: The range (array constant) created by the IF function is stored in Excel’s memory, not in a range. The array constant looks like this:

{1;0;1;0;1;0;1;0}

This array constant is used as a parameter to the SUM function, which returns 4.

3. One more example: I want to count the number of rows that contain (Google and Stanford) or Columbia.

In addition, you can also see many other excel articles here.