Excel - How to count number of rows in range (i.e. various columns) that contain certain string?

Question

I have a list A of strings and I want to see if and if yes in how many rows each string is represented in range B. My data looks something like this:

List A:

   E
1 aaa
2 bbb
3 ccc

Range B:

   A      B      C
1 aaa    ---    ---
2 bbb    ccc    bbb
3 aaa    ---    ---

My desired result would be that in list A for "aaa" it would tell me 2, for "bbb" 1 and for "ccc" 1. Have been trying to do this with the below array formula but it doesn't seem to be working:

=SUM(IF(COUNTIF(Range B,List A($A1)>0,1,0))

Answer 1

As much as I like to avoid encouraging the use of the volatile OFFSET¹ function, it seems the most likely candidate in this situation.

        

The array formula in F2 is,

=SUM(SIGN(COUNTIF(OFFSET(A$2, ROW($1:$99)-1, 0, 1, 3), E2)))

Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula.

¹_{OFFSET is considered a volatile function that recalculates whenever anything in the workbook changes.}