Explanation
In this example, the goal is to count cells in the range D5:D15 that contain “red” or “blue”. For convenience, the D5:D15 is named color . Counting cells equal to this OR that is more complicated than it first appears because there is no built-in function for counting with OR logic. The COUNTIFS function will allow multiple conditions, but all conditions are joined with AND logic. The article below explains several options.
COUNTIF + COUNTIF
A simple, manual way to count with OR is to use the COUNTIF function more than once:
=COUNTIF(color,"red") + COUNTIF(color,"blue")
In both cases, the range argument inside COUNTIF is color (D5:D15). However, criteria is “red” in the first COUNTIF and “blue” in the second. The first COUNTIF returns 4 and the second COUNTIF returns 3, so the final result is 7. This formula works fine, but it is somewhat redundant.
COUNTIF with array constant
Another way to configure COUNTIF is with an array constant that contains more than one value to use for criteria . This is the method used in the example shown above:
=SUM(COUNTIF(color,{"red","blue"}))
Inside the SUM function, the COUNTIF function is given color (D5:D16) for range and {“red”,“blue”} for criteria :
COUNTIF(color,{"red","blue"}) // returns {4,3}
This causes the COUNTIF function to return two counts: one for “red” and one for “blue”. These counts are returned directly to the SUM function in a single array :
=SUM({4,3}) // returns 7
And SUM returns 7 as the result. In other words, COUNTIF returns multiple counts to SUM, and SUM returns a final result. This is an example of nesting one formula inside another.
For more complicated scenarios, see: COUNTIFS with multiple criteria and or logic
SUMPRODUCT function
Another way to solve this problem is with the SUMPRODUCT function like this:
=SUMPRODUCT((D5:D15="red")+(D5:D15="blue"))
This is an example of using Boolean logic. Inside SUMPRODUCT there are two expressions joined by the addition (+) operator . Because color contains 11 values, each expression creates an array with 11 TRUE and FALSE values:
=SUMPRODUCT({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}+{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE})
In the first array, the TRUE values correspond to cells that contain “red”. In the second array, the TRUE values correspond to cells that contain “blue”. When the two arrays are added together, the math operation converts the TRUE and FALSE values to 1s and 0s:
=SUMPRODUCT({1;1;1;0;1;0;1;0;1;1;0})
With just one array to process SUMPRODUCT sums the items in the array and returns 7 as a result. Another way to configure SUMPRODUCT is like this:
=SUMPRODUCT(--(D5:D15={"red","blue"}))
In this formula, the expression:
D5:D15={"red","blue"})
Returns a single array with 11 rows and 2 columns. The double negative coerces the TRUE and FALSE values to 1s and 0s:
=SUMPRODUCT({1,0;1,0;0,1;0,0;0,1;0,0;1,0;0,0;0,1;1,0;0,0})
And SUMPRODUCT again returns 7 as a final result.
For more complex scenarios see: SUMPRODUCT with multiple criteria and OR logic and Count cells equal to one of many things .
Double-counting risk
When counting with OR logic, be aware of the risk of double-counting. In this particular example, the values “red” and “blue” are values in the same field, so there is no danger in double-counting. However, if you are counting records where one field is “red” OR another field is “blue”, take care not to double-count , since both can be true at the same time in the same record.
Explanation
In this example, the goal is to count test scores in column C that are greater than 90. The simplest way to do this is with the COUNTIF function , which takes two arguments , range and criteria :
=COUNTIF(range,criteria)
All test scores are in the range C5:C16 and we want to count scores greater than 90 , so we configure COUNTIF like this:
=COUNTIF(C5:C16,">90") // returns 2
COUNTIF returns 2, since there are two scores in C5:C16 that are greater than 90. Notice that criteria is given as a text value in double quotes (""). This is a requirement of the COUNTIF function which is in a group of eight functions that use a special syntax for criteria . In this syntax, logical operators are joined with numeric values and provided as text.
Greater than or equal to
To count cells that are greater than or equal to , adjust the formula to use “>=” instead of “>”. In the example shown, the formula in F6 is:
=COUNTIF(C5:C16,">=90") // returns 3
Here, COUNTIF returns 3, since there are three scores in C5:C16 greater than or equal to 90.
Value in another cell
To adjust the formula to use a value in another cell as part of the criteria, you can concatenate the logical operator to the cell reference with the ampersand (&) operator like this:
=COUNTIF(range,">"&A1)
For example, with 90 in cell A1, the criteria will become “>90” after concatenation:
=COUNTIF(range,">"&A1)
=COUNTIF(range,">90")
=2
The result will again be 2. If the value in A1 is changed to a different number, COUNTIF will return a new result.
COUNTIFS function
The COUNTIFS function is designed to handle multiple criteria but can be used just like the COUNTIF function in this example:
=COUNTIFS(C5:C16,">90") // returns 2
=COUNTIFS(C5:C16,">=90") // returns 3
Video: How to use the COUNTIFS function
