Count cells that contain either x or y in a range in Excel

To count cells that contain either one value or another, some Excel users may add two COUNTIF functions together to deal with it, however, this method counts cells with AND logic (contain both x and y) instead of OR logical (contain either x or y). To avoid double counting the criteria, this tutorial is going to show you how to apply a formula based on the SUMPRODUCT function in details to count the number of cells in a specific range that contain either x or y in Excel.

How to count cells that contain either x or y in a range in Excel

As the below screenshot shown, there is a data range B3:B9, to count the number of cells in this range that contains either “KTE” or “KTO”, please apply the below formula to get it done.

Generic Formula

=SUMPRODUCT(--((ISNUMBER(FIND("criteria1",rng)) + ISNUMBER(FIND("criteria2",rng)))>0))

Arguments

How to use this formula?

1. Select a blank cell to output the result.

2. Enter the below formula into it and press the Enter key to get the result.

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

How these formulas work?

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

• 1. FIND(D3,B3:B9): The FIND function checks for whether the value “KTE” in D3 existing in the specified range (B3:B9), and returns an array: {1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,1,#VALUE!}.
  In this array, the two 1s represent that the first and the second to last cells in the list B3:B9 containing value “KTE”, the #VALUE!s represent the value “KTE” does not find in other cells.
• 2. ISNUMBER{1,#VALUE!, #VALUE!, #VALUE!, #VALUE!,1, #VALUE!}: The ISNUMBER function returns TURE if meeting number in the array, and returns FALSE if meeting errors, and finally returns the new array as {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE}.
• 3. FIND(D4,B3:B9): This FIND function also checks for whether the value “KTO” in D4 existing in the specified range (B3:B9), and returns an array: {#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}.
• 4. ISNUMBER{#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}: The ISNUMBER function returns TURE if meeting number in the array, and returns FALSE if meeting errors, and finally returns the new array as {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• 5. {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE} + {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}: Here the two arrays returns the result as {1;1;0;0;0;1;0}.
• 6. {1;1;0;0;0;1;0}>0: Here compares whether each number in the array is greater than 0 and returns the result as {TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE}.
• 7. --({TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE}): These two minus signs convert “TRUE” into 1 and convert “FALSE” into 0. Here you will get a new array as {1;1;0;0;0;1;0}.
• 8. =SUMPRODUCT({1;1;0;0;0;1;0}): The SUMPRODUCT function sums all number in the array and returns the final result as 3 in this case.

Related functions

Related formulas