Exceljet

Quick, clean, and to the point

Count columns that contain specific values

Excel formula: Count columns that contain specific values
Generic formula 
=SUM(--(MMULT(TRANSPOSE(ROW(data)^0),--(criteria))>0))
Summary 

To count rows that contain specific values, you can use a formula based on the MMULT, TRANSPOSE, ROW, and SUM functions. In the example shown, the formula in I6 is:

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

where data is the named range B4:F15. The result is 4, the number of columns 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 BYCOL function.

Explanation 

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 array2Array1 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))))

Using BYCOL to count columns that contain a specific value

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.