0

I am pretty good on Excel but this has got me.

I have a range of entries - $D7:$AH7

I want to know how many times these entries appear in this range - $K$7:$V$10

I have tried countif array but I think it is trying to match row with row rather than each entry in my range against the entire lookup range. I also tried transposing the lookup range onto the same line but then I think it tries to match column by column in the same fashion.

If sumproduct works I can't use that is too calculation extensive with this document.

I am happy to use a VBA solution if you have that suggestion.

Community
  • 1
  • 1
Kenny
  • 3
  • 2

1 Answers1

0

Non VBA solution:

=SUMPRODUCT(COUNTIF($K$7:$V$10,$D7:$AH7))

VBA solution:

Founds how many times values from rng1 appears in rng2:

Can it ignore blanks?

UPD:

Function countEntries(rng1 As Range, rng2 As Range) As Long
    Dim arr1, arr2, a1, a2

    arr1 = rng1.Value
    arr2 = rng2.Value

    For Each a1 In arr1
        If Trim(a1) <> "" Then
            For Each a2 In arr2
                If a2 = a1 Then
                    countEntries = countEntries + 1
                    Exit For
                End If
            Next
        End If
    Next
End Function

call it like this: =countEntries($D7:$AH7,$K$7:$V$10).

P.S. I use loop for determining whether array contains value or not rather than Application.Match/Application.CountIf because it's up to 10 times faster. See this link for details: Matching values in string array

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • This VBA solution is awesome but is also matching blanks which causes me a problem. Can it ignore blanks? – Kenny Apr 28 '14 at 11:57
  • simoco.... I'm no VBA expert but have just made the following adjustment to account for blank entries. If you have a better solution than this edit please let me know. Thanks for your help either way, got me out of a bind ;) If a2 = "" Then ' do nothing Else – Kenny Apr 28 '14 at 12:13
  • yeah, `If a2 = "" Then` is pretty good, but it seems that it'd be slightly better to add same condition for `a1`. See my updated answer – Dmitry Pavliv Apr 28 '14 at 12:24