0

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Shahid Thaika
  • 2,133
  • 5
  • 23
  • 59
  • 1
    That's just a [COUNTIFS function](https://support.office.com/en-us/article/COUNTIFS-function-53C4DC8E-0E5B-4E32-93DF-9CA5E7DA89ED). You are going to have trouble getting the [SUMPRODUCT function](https://support.office.com/en-us/article/SUMPRODUCT-function-57a7bfa7-f74d-4ead-8c93-57f759c8f616) working as a [WorksheetFunction object](https://msdn.microsoft.com/en-us/library/office/ff834434.aspx). Maybe the [Application.Evaluate method](https://msdn.microsoft.com/en-us/library/office/ff193019.aspx) would be better. –  Aug 23 '15 at 09:19
  • To put more context, I am reading a couple of tables into variables and reading and writing to it, instead of cells to optimise my code. Hence, would prefer anything that doesn't read from a range or I/O – Shahid Thaika Aug 23 '15 at 09:27
  • For all intents and purposes, a COUNTIFS is just the [UBound](https://msdn.microsoft.com/en-us/library/office/gg278658.aspx) on a filtered data set. See [Filtering 2D Arrays in Excel VBA](http://stackoverflow.com/questions/10450645/filtering-2d-arrays-in-excel-vba). –  Aug 23 '15 at 09:51
  • Please put that in a reply and I'll choose. I have actually written my own VBA function for now, but in the future, I will definitely consider the filtered approach – Shahid Thaika Aug 23 '15 at 10:07

1 Answers1

1

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.

Community
  • 1
  • 1