COUNTIF using arrays

[YasserKhalil]

Hello everyone
I am searching for UDF that do the same as COUNTIF but within arrays
I would store A2:E20 in an array and rely on the udf to do the countif or countifs
If possible I would like it to be more flexible to deal with one criteria or two or more ..
I imagine CountifsInArray(array,col num in array,criteria1,col num in array,criteria2 ...here if possible to be as COUNTIFS)
Here's sample to work on
Thanks advanced for help

Test

 

[Rick Rothstein]

Have a look at this code

Sub Test()    Dim arr
    arr = Range("A2:E20").Value
    
    '=COUNTIF(B2:B20,"A")
    'This is used with ranges ...
    'Imagine ::::
    MsgBox COUNTIFINARRAY(arr, 2, "A")
    '>> 2 is the second column in the array and "A" is the criteria ..
End Sub

How flexible do you need the COUNTIFINARRAY function to be? Will the function always be looking for an equal condition (like your code is doing with its implied ="A"? Will relational conditions (less than, greater than, etc.) need to be supported? Will wild cards need to be supported? Also, will your array always be a two-dimensional array created from a worksheet range (as your code example shows)?

 

[AliGW]

'Imagine ::::

That is what we do not want to do. We want you to show us what your results will look like so that we can understand what you are imagining.

 

[svendiamond]

I guess he just wants to be able to use COUNTIFS but the range changes and he doesn't want to have to change the column names.

So if the range changed to AB2:AF20 he could still use the same formula. But it is amazing how difficult it is to get some of these people to explain their problem correctly and show us their expected results.

 

[Aladin Akyurek]

Thanks for reply
The issue is not a problem with COUNTIFS itself
I can use it in worksheet directly and also using worksheet function built in feature
I have vba array which is a result of many operations and I don't deal at this point with ranges ..I am dealing with the arrays so I think of UDF to do the same task of COUNTIFS but to deal with VBA arrays

It's true that...

1.

=COUNTIFS({"jad";"kad";"jad"},"jad",{4;4;4},4)

2.

=countifs(left({"nad";"vad";"nan";"kad";"nax"}),"n")

and so on won't succeed.

But you can handle these cases with array-processing formulas...

ad 1. Control+shift+enter, not just enter:

=SUM(IF({"jad";"kad";"jad"}="jad",IF({4;4;4}=4,1)))

ad 2. Control+shift+enter, not just enter:

=SUM(IF(LEFT({"nad";"vad";"nan";"kad";"nax"})="n",1))

 

[YasserKhalil]

As for the arrays will be always two dimensional array ..
Relational conditions (less than, greater than, etc.) need to be supported ..
Wild cards need to be supported ..
Results would be like the same results of built-in COUNTIFS .. the same results exactly (I just need to change the technique used of counting)
built-in countifs used ranges then criteria but the needed approach to use vba arrays instead of ranges and not to repeat array for each criteria ..
Just to refer to one array then :: column number in the array then criteria - column number in the array then criteria - column number in the array then criteria ......

[/COLOR][COLOR=#333333][I]COUNTIFINARRAY(arr, 2, "A",3,"S")[/I][/COLOR][COLOR=#333333]

 

[svendiamond]

(I just need to change the technique used of counting)

It sounds like you don't need to change the technique used for counting... I don't understand why you are having such a difficult time explaining your issue to us, and especially why you can't provide us a clear example with expected results so we can attempt to duplicate them. Was I right in saying you want the exact same as COUNTIFS but you want it to refer to a dynamic range? For instance, when the range changes from A:E, to M:Q, but the fields are still in the same relative location, you would want to be able to use your same COUNTIFS formula without having to change the column names? Is this correct? Please acknowledge some of the previous posts and reply to them specifically. We're asking questions and you're not giving us any answers - you just keep repeating the same thing. We want to help you but you have to help us in order to find a solution.

 

[YasserKhalil]

Thanks a lot for reply
As for the example was attached in the first post and to illustrate more this is a code for what I need (to make the request clearer)

Sub TestFunction()    Dim a() As Variant
    Dim x As Long
    
    a = Range("A2:E20")
    x = CountMatch(a, 2, "A", 5, "F")
    MsgBox x & " Match(es) Found.", vbInformation
End Sub


Function CountMatch(arr As Variant, col1 As Long, crit1 As Variant, Optional col2 As Long = 0, Optional crit2 As Variant = "") As Long
    Dim r1 As Long, r2 As Long, i As Long, n As Long


    Application.ScreenUpdating = False
        If IsArray(arr) Then
            n = UBound(arr, 2) + 1
            If col1 > 0 And col1 <= n Then
                For i = 1 To UBound(arr)
                    If arr(i, col1) = crit1 Then r1 = r1 + 1
                    If col2 > 0 And col2 <= n Then
                        If arr(i, col1) = crit1 And arr(i, col2) = crit2 Then r2 = r2 + 1
                    End If
                Next i
            End If
        End If
        If col2 = 0 Then CountMatch = r1 Else CountMatch = r2
    Application.ScreenUpdating = True
End Function

The code is ok for two criteria and deal with equal operator only
How can it be developed to deal with other operators < > & wild cards .. and how can adapt more criteria?

 

[svendiamond]

Just add "crit3" and "col3" to the function...

And it should work with other operators and wildcards. Just change your criteria. Try instead of "A" try ">1" or whatever you want to test it out. And use an asterisk to test the wildcards.

 

[YasserKhalil]

As for the sample workbook provided in the first post, I tried this line and got 0 matches

Sub TestFunction()    Dim a() As Variant
    Dim x As Long
    
    a = Range("A2:E20")
    x = CountMatch(a, 2, ">50")
    MsgBox x & " Match(es) Found.", vbInformation
End Sub

 

[Ombir]

You're still not providing actual expected results from this! Literally give us an example of a COUNTIFS and the result it would return!

Regardless, there is no difference between =COUNTIFS(B:B,"A") and your theoretical =Countifsarray(array, 2, "A") they would both just be counting the appearances of "A" in that column.

Hi,

Op wants a function in VBA that would work similar to countif/countifs worksheet function. These two functions only accept range as an argument but Op data is in vba array and he wants to calculate count on basis of one or more criteria.

Hope this help you.