Excel Forum

Code: Select all

Private Sub CommandButton2_Click()
    Dim countResult As Long
    Dim ws As Worksheet
    Dim criteriaValue1 As Variant
    Dim criteriaValue2 As Variant
    Dim criteriaValue3 As Variant

    Set ws = ThisWorkbook.Sheets("Record") ' Change "Record" to the name of your specific sheet

    criteriaValue1 = Worksheets("Summary").Range("C2").Value
    criteriaValue2 = ">=" & Worksheets("Summary").Range("E1").Value
    criteriaValue3 = "<" & Worksheets("Summary").Range("F1").Value

    countResult = Application.WorksheetFunction.CountIfs(ws.Range("F:F"), criteriaValue1, ws.Range("C:C"), criteriaValue2, ws.Range("C:C"), criteriaValue3)

    ' Write the countResult to cell E2 in the "Summary" worksheet
    Worksheets("Summary").Range("E2").Value = countResult
End Sub