Assume that you have a large dataset with multiple columns, and you need to identify and count the number of cells in a particular column that do not contain specific strings. For instance, let us consider a scenario where we need to count the number of cells in a column that do not contain the words “cat” or “dog.”
Using ISNUMBER and SEARCH Functions
The ISNUMBER function in Excel checks whether a given value is a number or not. On the other hand, the SEARCH function searches for a specific string within another string and returns the position of the first character of the searched string. Using these two functions in conjunction can help us identify the cells that do not contain the specified strings.
To achieve this, we can use the following formula in a new column adjacent to the original column:
=ISNUMBER(SEARCH("cat",A2))+ISNUMBER(SEARCH("dog",A2))Here, A2 refers to the cell that we want to search for the specified strings. The formula returns a value of 0 if either of the specified strings is not present in the cell and a value greater than 0 if the cell contains either of the specified strings. Now, we can simply filter the new column to show only the cells that contain a value of 0.
Using MMULT, TRANSPOSE, and REDUCE Functions
The MMULT function in Excel multiplies two matrices and returns the sum of the products. The TRANSPOSE function, as the name suggests, transposes a matrix. The REDUCE function reduces a range of values to a single value by performing a specified operation on each element of the range.
To count the number of cells without specific strings, we can use the following formula:
=SUM(--(MMULT(--(TRANSPOSE(--(ISNUMBER(SEARCH({"cat","dog"},A2:A10))))),ROW(A2:A10)^0)=0))Here, A2:A10 refers to the range of cells that we want to search for the specified strings. The formula returns the count of cells that do not contain either of the specified strings. The use of the double unary operator (–) converts TRUE/FALSE values to 1/0, respectively. The use of the ROW(A2:A10)^0 generates an array of ones, which is multiplied by the matrix of converted TRUE/FALSE values. The result is then summed up to give the count of cells that do not contain either of the specified strings.
Thank you.
