But we need a more advanced formula to count columns that may contain multiple instances of a specific value.
MMULT option
One option for solving this problem is theMMULT function.
The MMULT function returns the matrix product of two arrays, sometimes called the “dot product”.
The result from MMULT is anarraythat contains the same number of rows asarray1and the same number of columns asarray2.
The MMULT function takes twoarguments,array1andarray2, both of which are required.
The column count inarray1must equal the row count inarray2.
TheROW functionis used to generate a numeric array of the right size.
This is the data we can use to solve the problem.
Each non-zero number represents a column that contains the number 19.
The result from MMULT is anarraythat contains the same number of rows asarray1and the same number of columns asarray2.
COLUMN Function
The Excel COLUMN function returns the column number for a reference.
For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet.
When no reference is provided, COLUMN returns the column number of the cell which contains the formula.
BYCOL can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
