CODEWITHSUNDEEP
vbaexcelworksheet-function
Shahid Thaika
Shahid Thaika

Reputation: 2305

(Condition) in SUMPRODUCT WorksheetFunction

In reality, I need to run a COUNTIFS WorksheetFunction using Variant Arrays, but guess this is not possible in VBA. The alternative would be to write my own COUNTIFS function in VBA, but I was wondering whether it'd be possible to write the following SUMPRODUCT function in VBA...

=SUMPRODUCT(--(Table1[Col1]="Something"),--(Table1[Col2]="Something"))

If we can do that, then I'd not need to write my extra function. Not sure which would be faster though.

Upvotes: 0

Views: 297

Answers (1)

user4039065
user4039065

Reputation:

If you are just looking for an operational COUNTIFS function using structured table references then the ListObject object and its properties is the way to go.

Sub dermal()
    Dim hdr1 As Long, hdr2 As Long, cntif As Long
    Dim str As String, app As Application

    Set app = Application

    With Worksheets("Sheet1").ListObjects("Table1")
        hdr1 = app.Match("Col1", .HeaderRowRange.Rows(1), 0)
        hdr2 = app.Match("Col2", .HeaderRowRange.Rows(1), 0)

        str = "something"
        cntif = app.CountIfs( _
            .ListColumns(hdr1).DataBodyRange, str, _
            .ListColumns(hdr2).DataBodyRange, str)
        Debug.Print cntif

    End With

    Set app = Nothing
End Sub

The newer COUNTIFS is much quicker that a comparable SUMPRODUCT function; typically taking 30% of the time to complete equivalent operations even when used with full column range erferences while SUMPRODUCT's have been trimmed down to the barest extents of the data.

If you absolutely need to squeeze out every milli-second and require variant arrays with in-memory processing then I would recommend a class structure using the filtering methods in Filtering 2D Arrays in Excel VBA by assylias.

Upvotes: 1

Related Questions