Exceljet

Quick, clean, and to the point

Count rows with multiple OR criteria

Excel formula: Count rows with multiple OR criteria
Generic formula 
=SUMPRODUCT(--((criteria1)+(criteria2)>0))
Summary 

To count rows using multiple criteria across different columns – with OR logic – you can use the SUMPRODUCT function. In the example shown, the formula in H7 is:

=SUMPRODUCT(--((C5:C11="blue")+(D5:D11="dog")>0))

The result is a count of rows where Color is "blue" or Pet is "dog".

Explanation 

In the example shown, we want to count rows where the color is "blue", OR the pet is "dog". This can be done with Boolean logic and the SUMPRODUCT function like this:

=SUMPRODUCT(--((C5:C11="blue")+(D5:D11="dog")>0))

The core of this formula is two logical tests, one for each condition:

(C5:C11="blue")+(D5:D11="dog")

In Boolean algebra, OR logic requires addition, so the two logical tests are joined by addition (+). The first logical test checks if Color is "blue":

(C5:C11="blue") // returns {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

Because we are testing 7 values in the range C5:C11, the result is an array of 7 TRUE/FALSE values. The second logical test checks if Pet is "dog": 

(D5:D11="dog") // {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}

Again, we are checking 7 values, so we get back an array that contains 7 results. 

The two arrays are then added together. The math operation automatically coerces the TRUE FALSE values to 1s and 0s and the result is a single array like this:

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

We can't simply add these values up with SUMPRODUCT because that would double count rows with both "blue" and "dog". So, to handle this situation, we use ">0" together with the double negative (--) to force all values to either 1 or zero:

--({2;0;1;1;1;0;1}>0)

These operations create a single array of 1s and 0s inside the SUMPRODUCT function:

=SUMPRODUCT({1;0;1;1;1;0;1}) // returns 5

With only one array to process, SUMPRODUCT simply returns the sum of the elements in the array.

Other logical tests

The example shown tests for simple equality, but you can replace those statements with other logical tests as needed. For example, to count rows where cells in column A contain "red" OR cells in column B contain "blue", you could use a formula like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("red",A1:A10))+ISNUMBER(SEARCH("blue",B1:B10))>0))

See more information about ISNUMBER with SEARCH here.

More conditions

To handle more conditions, just add more logical tests inside the SUMPRODUCT function, using parentheses to control the order of operations where necessary.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.