Storing CountIfs arguments in an array?

[fomoz]

hello, i'm trying to make a user-customizable workbook. the user will be able to customize the "Settings" sheet and type in fields and criteria that they want to compare.

the program does a WorksheetFunction.CountIfs method with multiple criteria. i was just wondering if it's possible to pass a variant array of arguments to this method, so that i don't have to change the code when the number of criteria changes.

right now it looks like this:

Please Login or Register  to view this content.

so the criteria will be read from the "Settings" sheet into the array and then it will resize automatically to fit the number of arguments.

or is there another way that i can do this?

thanks!

[DonkeyOte]

If you want the COUNTIFS itself to be dynamic you will (I think) need to build a formula string and use Evaluate to execute.

If you want to pass a variable number of range arguments to your routine (to reflect varying number of tests) you might consider using a ParamArray

It might be simpler to post a sample file which demonstrates what you're trying to achieve with the COUNTIFS element
Include a few examples which illustrate the "varying number of conditions"

It might even be the case (pending scenario) that a standard/native COUNTIFS function would suffice using wildcards etc to handle irrelevant conditions

[fomoz]

ok, a workaround would be to use "Select Case" for each range/criteria pair.

Please Login or Register  to view this content.

the question is: is there a way to do this without doing "Select Case"? is this more clear?

[DonkeyOte]

Yes, I understood the concept but not so much the reasoning behind it.

In my prior post I mentioned Evaluate, ParamArray etc...

Your sample codes have thus far been sub routines as opposed to functions so it remains unclear how you're passing the (to be) variable number of ranges for use in the COUNTIFS.
For this reason I suggested posting a sample file to illustrate your anticipated approach - at present it's a little bit of a mystery.

[fomoz]

Your sample codes have thus far been sub routines as opposed to functions so it remains unclear how you're passing the (to be) variable number of ranges for use in the COUNTIFS.

the number of range/criteria pairs is read from a cell on the Settings sheet. it is chosen by the user (1,2, or 3).

[DonkeyOte]

the number of range/criteria pairs is read from a cell on the Settings sheet. it is chosen by the user (1,2, or 3).

There is nothing in your code examples thus far that give any indication of the above nor where the ranges themselves are being detailed

To reiterate a point made previously...

If you want to build a COUNTIFS function dynamically with a variable number of arguments (ie not with Select Case) then you will need to build the function as a string and execute with Evaluate [using an appropriate parent object]

At this point I'll have to bow out I'm afraid - with so little meaningful information available passing constructive comment is proving difficult and I can only repeat points made previously.

Hopefully someone else will be better able to interpret your requirements.

[fomoz]

i understand your point. doing Evaluate is not an option though (performance considerations). thanks!

[DonkeyOte]

i understand your point. doing Evaluate is not an option though (performance considerations). thanks!

Are you implying that:

Please Login or Register  to view this content.

is going to be noticeably slower than:

Please Login or Register  to view this content.

If the difference between the two in performance terms is a very real concern for you then I'm afraid you have "much bigger fish to fry" than worrying about the COUNTIFS element of your model.

edit:
If you're running mass COUNTIFS with huge precedent ranges and want to optimise then you should consider (if not already)

a) minimising precedent ranges as much as possible

b) using/automating intermediate Pivots and GETPIVOTDATA for results

option b) may seem bizarre but the performance gains can be immense

[fomoz]

here's a sample file. it generates random numbers and then runs single criteria CountIf (my manual version and the built-in version). if you can get equal or better times than the BuiltInCountIf version by using Evaluate or any other way, please post your solution. thanks.

[DonkeyOte]

I didn't state Evaluate would be faster simply that if the difference was significant to your process then there may be other more significant issues.

In terms of

a) creating a COUNTIFS out of your existing COUNTIF approaches

b) being able to handle a variable # of ranges & criteria count whilst avoiding Evaluate

You could persist with single condition COUNTIF by virtue of delimited "What" & "Where" Arrays
(ie joining all columns for a given item into a delimited string)

The resulting delimited arrays would need to be written back to the Worksheet prior to the various COUNTIFs being fired - then based upon the delimited arrays

The difference between COUNTIF (delimited "keys" & criteria) and COUNTIFS when used natively is negligible.

Populating the delimited arrays should be pretty fast.

Please Login or Register  to view this content.

This technique of creating delimited keys etc is a common workaround pre XL2007 to negate need for SUMPRODUCT & Arrays which are obviously very poor performers in the efficiency stakes.

[DonkeyOte]

I just got around to creating this pseudo-COUNTIFS approach based largely on your existing routine

Please Login or Register  to view this content.

takes around 4 seconds to process 1000 calculations based on 40000 x 6 matrix (and 6 criteria)

[fomoz]

I just got around to creating this pseudo-COUNTIFS approach based largely on your existing routine

takes around 4 seconds to process 1000 calculations based on 40000 x 6 matrix (and 6 criteria)

right now we're looking at improving CountIf with a single criteria, then we can extend that to mutiple criteria. what you did is just concatenate the cells and check if they match the concatenated result in the other range. of course it's faster since you're working with one column instead of six.

[fomoz]

actually whatever dude, just forget about it.

[romperstomper]

Here's a thought: when someone takes the time to try and help you, have the common courtesy to acknowledge that and show some appreciation. Otherwise you'll find very few people willing to try and assist you in future.

[fomoz]

Here's a thought: when someone takes the time to try and help you, have the common courtesy to acknowledge that and show some appreciation. Otherwise you'll find very few people willing to try and assist you in future.

lol thanks bro, but i'll take my chances.

[shg]

Please find another forum. We're not smart enough to help you.

Bye.

[shg]

BTW, DO, I like your analysis.