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:




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 -


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

enter image description here

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


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


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
    countrow = a
End Function

like -

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.