CODEWITHSUNDEEP
vbaexcel
user1664305
user1664305

Reputation: 179

VBA Countif two sets of criteria match

I am trying to count each "Three" if and only if it follows "Two". I have a list of data in one column (G1:G100) that alternates between "One, Two and Three" and I am just looking for the count of how many times three follows two.

The code I was using searches for each instance and adds the but as I explained I want it to count only each instance if the condition is met.

Sub sample()
Dim wsMain As Worksheet: Set wsMain = ThisWorkbook.Sheets("sheet1")
Dim k As Long

'get count of cells that start with ring
k = Application.WorksheetFunction.CountIf(Columns(7), "Two") + Application.WorksheetFunction.Countif(Columns(7)

If k > 0 Then
    Range("C2").Value = k
    End If


End Sub

Upvotes: 1

Views: 3907

Answers (2)

user4039065
user4039065

Reputation:

The criteria_range cell ranges in a COUNTIFS function have to be the same size but they do not have to be on the same plane; i.e. they can be offset to each other.

Sub two_three()
    Dim rng As Range, k As Long
    With Sheets("Sheet1")
        Set rng = Intersect(.Columns(7), .UsedRange)
        k = Application.CountIfs(rng, "two", rng.Offset(1, 0), "three")
        If k > 0 Then
            .Range("C2").Value = k
        End If
    End With
End Sub

You cannot offset a full column reference because it already refers to every cell in the column and there is nowhere to offset. However, it is highly unlikely that the entire column is being used so starting with the column truncated to the extent of the Worksheet.UsedRange property should allow you to offset 1 row down for the second condition.

Upvotes: 1

Ditto
Ditto

Reputation: 3344

Why do you want to use VBA for this? You'd probably need to do a loop to do it in VBA,

If you avoid VBA, you could just do:

1) Create a formula in H1: =AND(G1="Two",G2="Three") 2) copy that formula down: H2:H100 3) in whatever other cell you need the final answer: =COUNTIF(H:H,TRUE)

I would think that would be easier ?

[edit] if you really need to use VBA, just use a loop:

Sub test()
  Dim cnt As Integer
  cnt = 0
  For i = 1 To 100
    If Cells(i, 7).Value = "Two" And Cells(i + 1, 7).Value = "Three" Then
      cnt = cnt + 1
    End If
  Next i

  Range("C2").Value = cnt
End Sub

There might be a fancier way using some INDEX functions .. but I'm not sure if it's really worth it compared to the complexity level of above code ;) [/edit]

Upvotes: 0

Related Questions