Excel Formulas - Count cells that contain any of the specified string

Count cells that contain any of the specified string

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)
ISNUMBER(value)
FIND(find_text, within_text, [start_num])

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.
The ISNUMBER funtion returns True if the value refers to a number
The FIND function is used to locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

Explanation

To count number of cells that contain either one value or another. If we count cells with "OR" criteria there may be a chance to double count. For example if we are counting cells that contain both the "abc" and "pqr" by two COUNTIF function, there may be a chance to count double.
That is why, you can use SUMPRODUCT with an ISNUMBER and FIND combination. The formula written in cell F7

Formula

=SUMPRODUCT(--(ISNUMBER(FIND("abc",B5:B10)) +  ISNUMBER(FIND("pqr",B5:B10))>0))

How this formula works

Inside the formula the ISNUMBER(FIND("abc",B5:B10)) and ISNUMBER(FIND("pqr",B5:B10)) works as - the FIND function returns the number of the position where the text found and ISNUMBER function converts it into True or False and make an array like -

{FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;} and {FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;}

Now these two arrays are adding and making a new array like -

{FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;}

From the two arrays the cell B8 contain both "abc" and "pqr". So, they will be added twice, that is why >0 have been added to get it once.
Therefore the -- will convert the True and False in 1 and 0's and at last the SUMPRODUCT will give the result.

Count cells that contain any of the specified string using range name and criteria name

Explanation

To count number of cells that contain either one value or another using two criteria variable the formula below have been used.

Formula

=SUMPRODUCT(--(ISNUMBER(FIND(E7,Sample_Text)) +  ISNUMBER(FIND(F7,Sample_Text))>0))

How this formula works

The formula works similer to the above. In this formula the cirteria variable have used as E7 and F7 and the searching range have assigned by a range name Sample_Text.

Check whether a cell contain either one or another value

Syntax of used function(s)

COUNTIF(criteria_range, criteria)

The COUNTIF function is used to count the number of cells that meet a certain criteria.

Explanation

To check wheather a cell contain any of the value given in the argument, the formula below can be used.

Formula

=--(SUM(COUNTIF(B5,{"*pqr*","*xyz*"}))>0)

How this formula works

In the above formula the cell B5 is the criteria range and {"*pqr*","*xyz*"} is the cirteria by a range name Sample_Text.

Previous: Excel Formulas - Count cells which are not equal either one or another cirteria
Next: Excel Formulas - Count number of cells that contain positive or negative numbers