Count columns that contain specific values

In this example, the goal is to count the number of columns in the data that contain 19 (the value in cell I4). The main challenge in this problem is that the value might appear in any row, and more than once in the same column. If we wanted to simply count the total number of times a value appeared in a range, we could use the COUNTIF function. But we need a more advanced formula to count columns that may contain multiple instances of a specific value. The explanation below discusses two options: one based on the MMULT function, and one based on the newer BYCOL function.

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 in array1 must equal the row count in array2. In the example shown, the formula in I6 is:

=SUM(--(MMULT(TRANSPOSE(ROW(data)^0),--(data=I4))>0))

Working from the inside out, the logical criteria used in this formula is:

--(data=I4)

where data is the named range B4:F15. This expression generates a TRUE or FALSE result for every value in data, and the double negative (--) coerces the TRUE and FALSE values to 1s and 0s, respectively. The result is an array of 1s and 0s like this:

{0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;1,0,0,1,0;0,1,0,1,0;0,0,0,1,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,1;0,0,0,0,0}

Like the original data, this array is 12 rows by 5 columns (12 x 5) and is delivered directly to the MMULT function as array2. Array1 is derived with this snippet:

TRANSPOSE(ROW(data)^0)

This is the tricky part of the formula. The ROW function is used to generate a numeric array of the right size. To perform matrix multiplication with MMULT, the column count in array1 (12) must equal the row count in array2 (12). ROW returns a 12-row array, which is raised to the power of zero, and converted with the TRANSPOSE function into a 12-column array:

=TRANSPOSE(ROW(data)^0)
=TRANSPOSE({4;5;6;7;8;9;10;11;12;13;14;15}^0)
=TRANSPOSE({1;1;1;1;1;1;1;1;1;1;1;1})
={1,1,1,1,1,1,1,1,1,1,1,1}

With both arrays in place, the MMULT function runs and returns an array with 1 row and 5 columns, {1,1,0,3,1}. This is the data we can use to solve the problem. Each non-zero number represents a column that contains the number 19. We can now simplify the formula to:

=SUM(--({1,1,0,3,1}>0))

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:

=SUM({1,1,0,1,1})

In this array, 1 represents a column that contains 19 and 0 represents a column that does not contain 19. The SUM function returns a final result of 4, the count of all columns that contain the number 19.

BYCOL option

The BYCOL function applies a LAMBDA function to each column in a given array and returns one result per column in a single array. The purpose of BYCOL is to process data in an array or range in a "by column" fashion. For example, if BYCOL is given an array with 5 columns, BYCOL will return an array with 5 results. In this example, we can use BYCOL like this:

=SUM(BYCOL(data,LAMBDA(col,--(SUM(--(col=I4))>0))))

The BYCOL function iterates through the named range data (B4:D15) one column at a time. At each column, BYCOL evaluates and stores the result of the supplied LAMBDA function:

LAMBDA(col,--(SUM(--(col=I4))>0))

Working from the inside out, the logic here checks for values in col that are equal to I4, 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 result from SUM is > 0 and coerce that result to a 1 or 0 with another double negative. After BYCOL runs, we have an array with one result per column, either a 1 or 0:

{1,1,0,1,1} // result from BYCOL

The formula can now be simplified as follows:

=SUM({1,1,0,1,1}) // returns 4

In the last step, the SUM function sums the items in the array and returns a final result of 4.

Literal contains

If you need to check for specific text values, in other words, literally check to see if cells contain certain text values, you can change the logic in the formula on this page to use the ISNUMBER and SEARCH function. For example, to count cells/rows that contain "apple" you can use:

=ISNUMBER(SEARCH("apple",data))

Details on how this formula works here.