The array formula works, but it takes too much computer power to calculate and will make the workbook larger in size. Use the COUNTIF formula with the conjunction of the wild card ("*").
IF CELL B1, contains the following values: a, b, c, d, e, f, g
and cell B2, contains the following values: a, b, c, d, e, f, g
You want to know how many times the value "a" shows up in column B across all the cells. The formula to use would be:
the formula should return quantity of 2, since a is showes up in cell B1 and B2.