[VBA] Something like COUNTIFS for Excel 2003

Well, there you go. A colleague sends you a copy of an Excel file and needs a nice formula. So I do the nice formula. Then I try to save the sheet back into the same form in which it arrived:

Clang!

Seems that Excel 2003 doesn't have the nice little COUNTIFS function that I have in my Excel 2007. What to do, what to do, what to do ...

Well, it'd be nice to install Office 07, but in the interim, how about a bit of VBA?

We'll call it RANGEDCOUNT, and it will accept a range of data, and a string being the criteria. The criteria can be

1. a single value preceded by an operation (>, >=, =, <=, <, #), or


2. a range of values, the upper and lower bounds separated by a dash.

Function RANGEDCOUNT(rData As Range, rCriteria As String) As Integer
        Dim i As Long
        Dim sOper As String
        Dim dAmt As Double
        Dim dUpper As Double
        Dim dLower As Double
        Dim bRanged As Boolean
        Dim iDash As Integer
        Dim dDataItem As Double
        Dim iCount As Integer
        iCount = 0
        bRanged = False

Right, that's all the declarations over with. Now to check whether we have a range specification or not and what to do if it's not. Notice the 'Instr(2' which checks for an equals sign after the first character, as we're checking to see if we have '>=' or '<=' and don't want to fail on a bare equals

        iDash = InStr(1, rCriteria, "-")
        If iDash = 0 Then
                If InStr(2, rCriteria, "=") Then
                        sOper = Left$(rCriteria, 2)
                        dAmt = Val(Mid$(rCriteria, 3))
                Else
                        sOper = Left$(rCriteria, 1)
                        dAmt = Val(Mid$(rCriteria, 2))
                End If

The else fires if we do in fact have a range setting and handles that appropriately.

        Else
                bRanged = True
                dLower = Val(Left$(rCriteria, iDash - 1))
                dUpper = Val(Mid$(rCriteria, iDash + 1))
        End If

Next, step through the data and for each element, apply the logic for either a ranged count or an operator count.

        For i = 1 To rData.Count '- 1 'LBound(rData.) To UBound(rData)
                dDataItem = rData(i).Value
                If bRanged Then
                        If dDataItem >= dLower And dDataItem <= dUpper Then
                                iCount = iCount + 1
                        End If
                Else
                        Select Case sOper
                        Case "="
                                If dDataItem = dAmt Then iCount = iCount + 1
                        Case "<"
                                If dDataItem < dAmt Then iCount = iCount + 1
                        Case ">"
                                If dDataItem > dAmt Then iCount = iCount + 1
                        Case "<="
                                If dDataItem <= dAmt Then iCount = iCount + 1
                        Case ">="
                                If dDataItem >= dAmt Then iCount = iCount + 1
                        Case "#"
                                If dDataItem <> dAmt Then iCount = iCount + 1
                        End Select
                End If
        Next

Okay, iCount should have the result, so put it in RANGEDCOUNT and end.

        RANGEDCOUNT = iCount
End Function

Not bad for 15 to 20 minutes work, and would've been faster if I'd remembered how to get data out of Ranges. Now my colleague is happier and I've had a chance to do some something interesting. Granted, it's not particularly fault tolerant, but it's enough for now. © Bruce M. Axtens, 2010