VBA: Complicated CountIf

Question

I'm trying to implement a CountIf UDF in VBA with multiple AND arguments. I have constructed the following code which gives me an app-defined or obj error.

Sub Count_PoA()
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row - 5
    End With

    For i = 6 To LastRow
        Count = 0
        For j = 17 To 58
            For k = 12 To 397 Step 13
                If Sheet9.Range("JA:KD & i") = Sheet2.Cells(1, j) And Sheet4.Cells(i, k) = "TRUE" And Sheet4.Cells(i + 4, k) = "TRUE" Then
                    Count = Count + 1
                End If
                Count = Sheet2.Range("B" & j)
            Next
        Next
    Next
End Sub

Ideally I'd like something equivalent to:

COUNIF(Dim someRange as Range, condition1, condition2, condtion3, ...) Where condition1 can be a value or expression and all the conditions are join together in AND condition

Example: COUNTIF(Range("JA:KD & i"), Sheet2.Cells(j,1) And Sheet4.Cells(i, k) = "TRUE" And Sheet4.Cells(i + 4, k) = "TRUE")

This will never work: Sheet9.Range("JA:KD & i"). I expect you probably want something like Sheet9.Range("JA" & i & ":KD" & i). However that will probably not work as expected either, because you're comparing an array of values with a single value. Which line of code are you having a problem with? — mkingston
– mkingston, Commented Jan 23, 2013 at 4:39
Also shouldn't count be outside the loop? Also I am not able to actually understand your example. Can u upload a screenshot or data example? — Siddharth Rout
– Siddharth Rout, Commented Jan 23, 2013 at 5:53
@user1982557 Which version of Excel? (well if it's a UDF, then version doesn't matter but beyond 2003 you have countif that supports multiple conditions).. — bonCodigo
– bonCodigo, Commented Jan 23, 2013 at 6:41
For Excel 2007 onwards, as @HeadofCatering mentioned use COUNTIFS, for excel 2003 use sumproduct to simulate — Larry
– Larry, Commented Jan 23, 2013 at 7:36

bonCodigo · Accepted Answer · 2013-01-23 07:05:09Z

Here is something you could try: I have taken my own sample data since we don't see your sheet. Most ranges are hardcoded, but you can change it and make it dynamic accordingly. The arithmetic operator is one thing you need to change manually e.g. <, >, =

Option Explicit

Sub myCountIFS()
Dim counts As Integer, i As Integer
Dim LastRow As Long
Dim vArray1 As Variant
Dim cnd1 As String, cnd2 As String, cnd3 As String

LastRow = Sheets(1).Cells(Sheets(1).Rows.Count, "B").End(xlUp).Row - 1
vArray1 = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Sheets(1).Range("B2").Resize(LastRow, 3).Value))

cnd1 = Sheets(1).Range("F2").Value
cnd2 = Sheets(1).Range("G2").Value
cnd3 = Sheets(1).Range("H2").Value

For i = LBound(vArray1) To UBound(vArray1)
    If vArray1(i, 1) = cnd1 And vArray1(i, 2) = cnd2 And vArray1(i, 3) < CInt(cnd3) Then
        counts = counts + 1
    End If
Next i

Sheets(1).Range("I2") = counts

End Sub

Output:

enter image description here

Collectives™ on Stack Overflow

VBA: Complicated CountIf

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related