# Excel - How to check if and in how many rows a certain value/string is present in range

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:

aaa

bbb

ccc

``````Range B:

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))
``````

Assuming your lists of strings are in 1 cell per row - you can use an array formula.

We'll say List A is in `A1:A3` and Range B is in `B1:B3`. To count for each one in list A you can use -

``````=COUNT(IF(FIND(A1,\$B\$1:\$B\$3)>0,1,0))
``````

But this is an array formula, so when you type it you finish by hitting Ctrl+Shft+Entr and you'll see curly brackets `{}`.

If Range B is in multiple cells you can just use something like -

``````=COUNTIF(\$B\$6:\$D\$8,A1)
``````

There may be another way with formulas, but I can't think of it. You can create a helper column for what you're trying to find using

``````=IF(ISERROR(MATCH(\$A\$1,\$B6:\$D6)),0,1)
``````

Drag it down the rows, then sum that column. But that's a lot of helper columns if you want to count it for each value..

If you're open to VBA, a UDF similar to this would work -

``````Public Function countrow(rng As Range, str As String) As Integer
Dim a As Integer
a = 0
For Each Row In rng
For Each c In Row
If InStr(1, str, c) Then
a = a + 1
Exit For
End If
Next
Next
countrow = a
End Function
``````

like -

``````=countrow(A6:C9,A1)
``````
• Yes, Range B consists of multiple columns. And what I want to know is not the total number of occurrences of a certain value/string (which is what a simple countif formula would give me), but in how many rows that value/string occurs. – F Bert Aug 16 '15 at 11:26
• @FBert see my edit, though these solutions may not be ideal for you – Raystafarian Aug 16 '15 at 12:00