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:

Sub MyCountIfsTest()
    
    'Columns where conditions will be checked
    Dim rngCol1 As Range
    Dim rngCol2 As Range
    Dim rngCol3 As Range
    
    'Cells which contain data that is being compared with the columns
    Dim rngCell1 As Range
    Dim rngCell2 As Range
    Dim rngCell3 As Range
    
    Dim rngResult As Range
    Dim avResult As Variant
   
    With WorksheetFunction
        'Type in ranges and cells manually for each condition (1, 2, 3)
        avResult = .CountIfs(rngCol1, rngCell1, rngCol2, rngCell2, rngCol3, rngCell3)
    End With
    
    'Instead I would like to do something like this:
    
    Dim avArguments As Variant          'Array for storing arguments to be fed to CountIfs
    Dim lTotalConditions As Long        'Total number of conditions (from Settings sheet)
    
    ReDim avArguments(1 To lTotalConditions)
    
    vaArray(1) = rngCol2
    vaArray(2) = rngCell2
    vaArray(3) = rngCol3
    vaArray(4) = rngCell3
    'etc..
    
    With WorksheetFunction
        avResult = .CountIfs(rngCol1, rngCell1, avArguments)
    End With
    
End Sub

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.

Sub MyCountIfsTest()
    
    'Columns where conditions will be checked
    Dim rngCol1 As Range
    Dim rngCol2 As Range
    Dim rngCol3 As Range
    
    'Cells which contain data that is being compared with the columns
    Dim rngCell1 As Range
    Dim rngCell2 As Range
    Dim rngCell3 As Range
    
    Dim rngResult As Range
    Dim avResult As Variant
    
    Dim lNumberOfCriteria As Long   'Number of criteria (specified by the user)
    
    With WorksheetFunction
        Select Case lNumberOfCriteria
            Case 1
                avResult = .CountIfs(rngCol1, rngCell1)
            Case 2
                avResult = .CountIfs(rngCol1, rngCell1, rngCol2, rngCell2)
            Case 3
                avResult = .CountIfs(rngCol1, rngCell1, rngCol2, rngCell2, rngCol3, rngCell3)
        End Select
    End With
End Sub

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:

Sub Example()
   Debug.Print [COUNTIFS(A1:A10,"x",B1:B10,"y")]
End Sub

is going to be noticeably slower than:

Sub Example()
    Debug.Print WorksheetFunction.CountIfs(Range("A1:A10"),"x",Range(B1:B10),"y")
End Sub

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.

ReDim avWhat(1 To rngWhat.Rows.Count)
ReDim avWhere(1 To rngWhere.Rows.Count)
For lRow = LBound(avWhat) To UBound(avWhat) Step 1
    avWhat(lRow) = Join(Application.Transpose(Application.Transpose(rngWhat.Rows(lRow))), "^")
Next lRow
For lRow = LBound(avWhere) To UBound(avWhere) Step 1
    avWhere(lRow) = Join(Application.Transpose(Application.Transpose(rngWhere.Rows(lRow))), "^")
Next lRow

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

Sub PseudoCountifs()
    Const sPROCEDURE_NAME = "PseudoCountIfs"
    Dim dStartTime As Double, dStopTime As Double, dColumnTime As Double, dCountIfStart As Double
    Dim rngWhat As Range, rngWhere As Range, rngResult As Range
    Dim lRow As Long, lCol As Long, lWhatLastRow As Long, lWhatLastCol As Long, lWhereLastRow As Long
    Dim avWhat As Variant, avWhere As Variant, avResult As Variant
    Application.ScreenUpdating = False
    lWhatLastRow = ControlsSheet.[WhatLastRow]
    lWhatLastCol = ControlsSheet.[WhatLastCol]
    lWhereLastRow = ControlsSheet.[WhereLastRow]
    With DataSheet
        Set rngWhat = .Range(.Cells(1, 1), .Cells(lWhatLastRow, lWhatLastCol))
        Set rngWhere = .Range(.Cells(1, 1), .Cells(lWhereLastRow, lWhatLastCol)).Offset(0, lWhatLastCol)
        Set rngResult = .Range(.Cells(1, 1), .Cells(lWhatLastRow, 2)).Offset(0, 1 + lWhatLastCol * 2)
    End With
    ReDim avWhat(1 To rngWhat.Rows.Count)
    ReDim avWhere(1 To rngWhere.Rows.Count)
    avResult = avWhat
    For lRow = LBound(avWhat) To UBound(avWhat) Step 1
        avWhat(lRow) = Join(Application.Transpose(Application.Transpose(rngWhat.Rows(lRow))), "^")
    Next lRow
    For lRow = LBound(avWhere) To UBound(avWhere) Step 1
        avWhere(lRow) = Join(Application.Transpose(Application.Transpose(rngWhere.Rows(lRow))), "^")
    Next lRow
    Set rngWhere = rngWhere.Offset(, rngWhere.Columns.Count).Resize(, 1)
    rngWhere.Value = Application.Transpose(avWhere)
    With rngResult
        .Clear
        .Columns(1).Value = Application.Transpose(avWhat)
    End With
    dStartTime = Timer
    dCountIfStart = Timer
    With WorksheetFunction
        For lRow = LBound(avWhat) To UBound(avWhat)
            If avWhat(lRow) <> Empty Then
                avResult(lRow) = .CountIf(rngWhere, avWhat(lRow))
            Else
                avResult(lRow) = 0
            End If
        Next lRow
    End With
    Debug.Print sPROCEDURE_NAME & " CountIf in: " & TimeConvert(Timer - dCountIfStart)
    rngResult.Columns(2) = Application.Transpose(avResult)
    dStopTime = Timer
    Debug.Print sPROCEDURE_NAME & " done in: " & TimeConvert(dStopTime - dStartTime)
    Application.ScreenUpdating = True
    MsgBox sPROCEDURE_NAME & " done in: " & TimeConvert(dStopTime - dStartTime)
End Sub

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

[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.