But we need a more advanced formula to count rows that may contain multiple instances of the value.
Background study
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 ofarray1must equal the row count ofarray2.
TheCOLUMN functionis used for convenience as a way to generate a numeric array of the right size.
Literal contains
To check for specific substrings (i.e.
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.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
