Counting with an “OR” criterion in Excel can be more difficult than it seems at first glance. In this article you will find some simple but useful examples.
- Let’s start simple. It is required to count the number of cells containing “Google” or “Facebook” (first column). Here is the formula that will do it:
=COUNTIF(A1:A8,"Google")+COUNTIF(A1:A8,"Facebook")=СЧЁТЕСЛИ(A1:A8;"Google")+СЧЁТЕСЛИ(A1:A8;"Facebook") - However, if we need to count the number of rows that contain either “Google” or “Stanford” (we are considering two columns at once), then we cannot simply use the formula containing the function twice COUNTIF (COUNTIF) (see figure below). In this case, lines that contain both “Google” and “Stanford” (on the same line) are counted twice, and they should only be counted once. 4 is the answer we need.
=COUNTIF(A1:A8,"Google")+COUNTIF(B1:B8,"Stanford")=СЧЁТЕСЛИ(A1:A8;"Google")+СЧЁТЕСЛИ(B1:B8;"Stanford")
- That’s why we need an array formula. We use the function IF (IF) to check if the string contains “Google” or “Stanford”:
=IF((A1="Google")+(B1="Stanford"),1,0)=ЕСЛИ((A1="Google")+(B1="Stanford");1;0)
- Explanation:
- Recall that TRUE = 1 and FALSE = 0.
- For line 1:
=ЕСЛИ(ИСТИНА+ИСТИНА;1;0) ► =ЕСЛИ(2;1;0) ► 1, i.e. the first line will be taken into account. - For line 2:
=ЕСЛИ(ЛОЖЬ+ЛОЖЬ;1;0) ► =ЕСЛИ(0;1;0) ► 0, i.e. the second line will not be taken into account. - For line 3:
=ЕСЛИ(ЛОЖЬ+ИСТИНА;1;0) ► =ЕСЛИ(1;1;0) ► 1, i.e. the third line will be taken into account, and so on.
- Now all we need is a function SUM (SUM), which will count units. To achieve this goal, we add a function SUM (SUM) and replace A1 on A1: A8, B1 on B1: B8.
=SUM(IF((A1:A8="Google")+(B1:B8="Stanford"),1,0)))=СУММ(ЕСЛИ((A1:A8="Google")+(B1:B8="Stanford");1;0)) - We finish entering the formula by pressing Ctrl + Shift + Enter.
Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. They do not need to be entered by yourself. They will disappear when you start editing the formula.
- Explanation:
- Range (array of constants) created with a function IF (IF) is stored in Excel memory, not in worksheet cells.
- The array of constants looks like this: {1;0;1;0;1;0;1;0}.
- This array of constants is used as an argument to the function SUM (SUM), giving a result of 4.
- We can take one more step forward. For example, count the number of lines that contain “Google” and “Stanford”, or “Columbia”.
=SUM(IF((A1:A8="Google")*(B1:B8="Stanford")+(B1:B8="Columbia"),1,0))=СУММ(ЕСЛИ((A1:A8="Google")*(B1:B8="Stanford")+(B1:B8="Columbia");1;0))
