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

 

[YasserKhalil]

Thanks a lot Ombir for your precise reply. That's exactly what I am seeking to tell all
You saved me

 

[Aladin Akyurek]

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.

The point is that there is no need for it. Moreover, COUNTIF(S) are designed to be range-processing functions, not array-processing. When you design one yourself, it will definitely be slower trying to behave like Excel's COUNTIFS for range processing and as an array-processing function in other cases.

 

[Ombir]

Moreover, COUNTIF(S) are designed to be range-processing functions, not array-processing.

Hi Aladin,

I agree that Countif(s) are designed for range processing but he wants similar function that can accept array as an input data argument.

The point is that there is no need for it.

I seldom use excel functions. Most of the time I work with VBA. So it would be incorrect to say that there is no need for it. If we are processing the data in memory then nobody wants to write the data into worksheet and use worksheet Countif(S) function and then again write the data back into array for further processing.

When you design one yourself, it will definitely be slower trying to behave like Excel's COUNTIFS for range processing and as an array-processing function in other cases.

Slowness is the topic for another day as OP is not concerned about this.

 

[svendiamond]

The point is that there is no need for it. Moreover, COUNTIF(S) are designed to be range-processing functions, not array-processing. When you design one yourself, it will definitely be slower trying to behave like Excel's COUNTIFS for range processing and as an array-processing function in other cases.

Plus, there is no reason to turn this range into a VBA array anyway. What advantage is it giving? COUNTIFS works perfectly... I'm genuinely interested in why OP needs to turn this into an array.

And yes, Ombir you make a valid point but OP has not given a single example of why this would be necessary. I think it would help us all understand and possibly come up with some code if we knew how this would be applied.

 

[Aladin Akyurek]

Hi Aladin,

I agree that Countif(s) are designed for range processing but he wants similar function that can accept array as an input data argument.



I seldom use excel functions. Most of the time I work with VBA. So it would be incorrect to say that there is no need for it. If we are processing the data in memory then nobody wants to write the data into worksheet and use worksheet Countif(S) function and then again write the data back into array for further processing.



Slowness is the topic for another day as OP is not concerned about this.

I have already made that point in my initial post. While at it, the OP should make all kindred functions also array-processing... I don't think the OP made a reasoned case for replicating range-processing COUNTIF(S) as also array-processing COUNTIFS...