Excel - How to count number of rows in range (i.e.

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

In the following image

B is the name for the range A1:C4. In F1 (next to the first element in your list A), enter:

=SUM(IF(COUNTIF(OFFSET(B,ROW(B)-1,0,1),E1)>0,1,0)

(entered with Ctrl + Shift + Enter) and copy down.

It should be flexible enough to handle a B which grows to more rows and columns.

Answer 2

There have already been some great answers posted here, but I thought I would add one more. If you are working with a large data set, this formula could come in handy in terms of speed. You can place the formula in F1 (See image below) and copy down. You will need to use CTRL + SHIFT + ENTER when entering the formula.

=SUM(IF(FREQUENCY(IF(E1=$A$1:$C$3,ROW($A$1:$C$3)),IF(E1=$A$1:$C$3,ROW($A$1:$C$3)))>0,1))

Answer 3

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.}