where data is the named range B4:D15. The result is 5, the number of rows that contain the number 19. Note: this is an array formula and must be entered with control shift enter in Legacy Excel. See below for a formula that performs the same task with the newer BYROW function.
Background study
The double negative in Excel formulas Boolean operations in array formulas Boolean algebra in Excel
MMULT option
One option for solving this problem is the MMULT function. The MMULT function returns the matrix product of two arrays, sometimes called the “dot product”. The result from MMULT is an array that contains the same number of rows as array1 and the same number of columns as array2. The MMULT function takes two arguments, array1 and array2, both of which are required. The column count of array1 must equal the row count of array2. In the example shown, the formula in G6 is: Working from the inside out, the logical criteria used in this formula is: where data is the named range B4:D15. This expression generates a TRUE or FALSE result for every value in data, and the double negative (–) coerces the TRUE FALSE values to 1s and 0s, respectively. The result is an array of 1s and 0s like this: Like the original data, this array is 12 rows by 3 columns (12 x 3) and is delivered directly to the MMULT function as array1. Array2 is derived with this snippet: Which returns an array of three 1s like this: This is the tricky and fun part of this formula. The COLUMN function is used for convenience as a way to generate a numeric array of the right size. To perform matrix multiplication with MMULT, the column count in array1 (3) must equal the row count in array2 (3). COLUMN returns the 3-column array {2,3,4} which, when raised to the power of zero, becomes {1,1,1}. Next, the TRANSPOSE function transposes the 1 x 3 array into a 3 x 1 array: With both arrays in place, the MMULT function runs and returns an array with 12 rows and 1 column, {2;0;1;0;0;0;0;2;0;0;1;1}. This array contains the count per row of cells that contain 19, and we can use this data to solve the problem. Each non-zero number represents a row that contains the number 19, so we can convert non-zero values to 1s and sum up the result: We check for non-zero entries with >0 and again coerce TRUE FALSE to 1 and 0 with a double negative (–) to get a final array inside SUM: In this array, 1 represents a row that contains 19 and a 0 represents a row that does not contain 19. The SUM function returns a final result of 5, the count of all rows that contain the number 19.
BYROW option
The BYROW function applies a LAMBDA function to each row in a given array and returns one result per row as a single array. The purpose of BYROW is to process data in an array or range in a “by row” fashion. For example, if BYROW is given an array with 12 rows, BYCOL will return an array with 12 results. In this example, we can use BYROW like this:
The BYROW function iterates through the named range data (B4:D15) one row at a time. At each row, BYCOL evaluates and stores the result of the supplied LAMBDA function: The logic here checks for values in row that are equal to G4, which results in an array of TRUE and FALSE values. The TRUE and FALSE values are coerced to 1s and 0s with the double negative (–), and the SUM function sums the result. Next, we check if the total from SUM is >0, and coerce that result to a 1 or 0. After BYROW runs, we have an array with one result per row, either a 1 or 0: The formula can now be simplified as follows: In the last step, the SUM function sums the items in the array and returns a final result of 5.
Literal contains
To check for specific substrings (i.e. check to see if cells contain a specific text value) you can adjust the logic in the formulas above to use the ISNUMBER and SEARCH functions. For example, to check if a value contains “apple” you can use: This expression would replace data=G4 logic above like this: See this example for more information on using ISNUMBER with SEARCH.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
