COUNTIFS with value to be tested being an array

Question

I am trying to perform a COUNTIFS function based on whether the cell value to be tested (the col sample data) appears in a array of values (Excel sample col).

Obviously to do a COUNTIFS is easy enough:

=COUNTIFS(  $E$3:$E$13,A6   ,$F$3:$F$13,"<"&EOMONTH($B$3,1), $F$3:$F$13, ">="&DATE(YEAR(B$3),MONTH(B$3),1), $G$3:$G$13, "<12")

Where the column E in the function is equal to one value, in this case a6. But where I am having issues is to test to check if in an array of values i.e. in this example the cells a5 & a6.

Has anyone any ideas? (aside from the obvious counting individually, there are a lot more than two values in the array!)

https://docs.google.com/spreadsheets/d/1QYbXwIdFEdjXqlLjF0b0jSusZNuqOMaUKVKZrjR4wko/edit?usp=sharing

David David · Accepted Answer · 2015-11-06T16:47:47

I couldn't find a solution to this using a few different combinations, the majority of functions I know need a single reference cell.

so VBA version:

Function ARRAYMATCHS(RefRng As Range, DataRng As Range, DateRng As Range, RefMonth As String, MonthsRng As Range, Months As Integer)

    Dim x As Integer
    Dim c As Integer
    x = 0
    c = 0

    For Each cell In DataRng
    c = c + 1
        If Not IsError(Application.Match(cell.Value, RefRng, 0)) Then
           If (MonthsRng(c, 1) > Months) Then
                If (Month(DateRng(c, 1)) = RefMonth) Then
                    x = x + 1
                End If
           End If
        End If
    Next cell

    ARRAYMATCHS = x

End Function

Which works fine but I would be interested in any native combinations of functions that could have done this!

COUNTIFS with value to be tested being an array

1 Answers