Countif in arrays with two conditions

[YasserKhalil]

Hello everyone
Mr. Rudi has helped me in similar issue at this link
http://www.eileenslounge.com/viewtopic.php?f=30&t=25266" onclick="window.open(this.href);return false;

But I don't need to rely on cells (colored cells are not required)

First have a look at this code that count the range("B8:AP8") on a criteria 4,3,2,1,0 ..Run the demo code to see the results in range("AR7:AV8")
I need to extend the criteria ..
To count if :
---------------
range("B8:AP8") on a criteria 4,3,2,1,0
range("B7:AP7") has one of the values in range("B1:F1")

The expected results would be like that snapshot

Untitled.png

for example : the number 3 happened 13 times in range("B8:AP8")
And in range("X7:L7") the numbers 16 & 18 & 26 & 28 occurs in this range
So the result for 3 is 4
Hope it is clear

[YasserKhalil]

I have posted the same issue here
http://www.excelforum.com/showthread.ph ... ost4530983" onclick="window.open(this.href);return false;

[HansV]

Here is a modified version of Rudi's macro:

Code: Select all

Sub Demo()
    Dim arr
    Dim arr2
    Dim i As Long
    Dim j As Long
    Dim temp(0 To 1, 0 To 4)
    Dim CountInArray As Long

    Application.ScreenUpdating = False
    arr = Range("B7:AP8").Value
    arr2 = Range("B1:F1").Value

    For i = 0 To 4
        CountInArray = 0
        temp(0, 4 - i) = i
        For j = LBound(arr, 2) To UBound(arr, 2)
            If Not IsError(Application.Match(arr(1, j), arr2, 0)) And arr(2, j) = i Then
                CountInArray = CountInArray + 1
            End If
        Next j
        temp(1, 4 - i) = CountInArray
    Next i

    Range("AR7").Resize(UBound(temp, 1) + 1, UBound(temp, 2) + 1).Value = temp
    Application.ScreenUpdating = True
End Sub

[YasserKhalil]

That's wonderful Mr. Hans
Thank you very very much for that great solution
I will try to work out the original file and see if I could twist it to suit my issue or not
Best Regards

[YasserKhalil]

Hello again Mr. Hans
Is it possible to edit the UDF implemented in the first post itself ..?
You can change the parameters of the udf to suit

[HansV]

Here you go:

Code: Select all

Function CountMatch(range1 As Range, matchrange As Range, range2 As Range, criteria As Variant)
    Dim i As Long
    Dim lngCount As Long
    If range1.Cells.Count <> range2.Cells.Count Then
        CountMatch = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To range1.Count
        If range2(i).Value = criteria Then
            If Not IsError(Application.Match(range1(i).Value, matchrange, 0)) Then
                lngCount = lngCount + 1
            End If
        End If
    Next i
    CountMatch = lngCount
End Function

Enter 0 to 4 in AR7:AV7.
Enter the following formula in AR8:

=CountMatch($B$7:$AP$7,$B$1:$F$1,$B$8:$AP$8,AR7)

Fill to the right to AV8.

[YasserKhalil]

That's really wonderful solution
You are great and brilliant

Can I use array parameters in the udf?
In fact I need the udf to deal with arrays not ranges in direct way so if you looked at the code in first post, you will notice the udf deals with the function
and I need the same with the new udf .. I mean to play it with arrays
I imagine :
CountMatch(arr,arrCrit,i)
arr >> range("B7:AP8")
arrCrit >> range("B1:F1")
i >> for numbers from 4 to 0
Thanks a lot for great help

[HansV]

You should be able to work that out yourself; I have given you the basic idea.

[YasserKhalil]

Since your last post and I was trying but no way. It doesn't work for me
I am not expert like you. I am just amateur

[YasserKhalil]

All what I can do is that to replace ranges with arrays

Code: Select all

Function CountMatch(arr As Range, arrCrit As Range, arr2 As Range, criteria As Variant)
    Dim i           As Long
    Dim lngCount    As Long
    
    If arr.Cells.Count <> arr2.Cells.Count Then
        CountMatch = CVErr(xlErrRef)
        Exit Function
    End If
    
    For i = 1 To arr.Count
        If arr2(i).Value = criteria Then
            If Not IsError(Application.Match(arr(i).Value, arrCrit, 0)) Then
                lngCount = lngCount + 1
            End If
        End If
    Next i
    
    CountMatch = lngCount
End Function

But I don't need to deal each row separately
I need to use one array for data and one for criteria

[YasserKhalil]

Can you fix that for me (It gives me error)

Code: Select all

Function CountMatch(arr As Range, arrCrit As Range, criteria As Variant)
    Dim i           As Long
    Dim lngCount    As Long
    
    For i = LBound(arr, 2) To UBound(arr, 2)
        If arr(2, i).Value = criteria Then
            If Not IsError(Application.Match(arr(1, i).Value, arrCrit, 0)) Then
                lngCount = lngCount + 1
            End If
        End If
    Next i
    
    CountMatch = lngCount
End Function

[Rudi]

My attempt...

In cell AR8, type: =CountMatchArr($B$8:$AP$8,$B$7:$AP$7,AR7)

Code: Select all

Function CountMatchArr(cat As Range, val As Range, crit As Range) As Variant
Dim c, v
Dim i As Long

    If cat.Cells.Count <> val.Cells.Count Then
        CountMatchArr = CVErr(xlErrRef)
        Exit Function
    End If
    c = Application.Transpose(cat)
    v = Application.Transpose(val)
    For i = LBound(c) To UBound(c)
        If c(i, 1) = crit Then
            If v(i, 1) = c(i, 1) Then
                CountMatchArr = CountMatchArr + 1
            End If
        End If
    Next i
End Function

[YasserKhalil]

Thanks a lot Mr. Rudi
What about range("B1:F1") ?
I need to deal with range("A7:AP8") and range("B1:F1") and the criteria which is in AR7

[Rudi]

Sorry...seems I jumped into the middle of this thread with out the full context.
I'll have to read through the thread again.

[HansV]

What is the point of using arrays here? It will make the code more complicated...

[YasserKhalil]

Mr. Hans the idea is that this will be used about 10 times in a sheet and then over 100 sheets
The file is so large and no place for formulas, so you find most of my issues are about arrays and dealing with arrays
Thanks for reply

[HansV]

Can you use this?

Code: Select all

Sub CountMatch2(range1 As Range, matchrange As Range, range2 As Range, destrange As Range)
    Dim arr1
    Dim arr2
    Dim arrm
    Dim i As Long
    Dim j As Long
    Dim n As Long
    Dim CountInArray As Long
    n = destrange.Columns.Count
    ReDim temp(1 To 2, 1 To n)

    Application.ScreenUpdating = False
    arr1 = range1.Value
    arr2 = range2.Value
    arrm = matchrange.Value

    For i = 0 To n - 1
        CountInArray = 0
        temp(1, n - i) = i
        For j = LBound(arr1, 2) To UBound(arr1, 2)
            If Not IsError(Application.Match(arr1(1, j), arrm, 0)) And arr2(1, j) = i Then
                CountInArray = CountInArray + 1
            End If
        Next j
        temp(2, n - i) = CountInArray
    Next i

    destrange.Value = temp
    Application.ScreenUpdating = True
End Sub

Example of usage:

CountMatch2 Range("B7:AP7"), Range("B1:F1"), Range("B8:AP8"), Range("AR7:AV8")

[YasserKhalil]

In fact Mr Hans It is greater than I imagined. Thank you very much for that Genius solution
You are BRILLIANT
Thanks a lot for this wonderful and incredible help