Trying to count data across and entire workbook. Scouring the Internet with no luck.

[epaprhasguy80]

Here are the key characteristics of my workbook:

• It has approximately 20 worksheets
• New worksheets are added monthly
• As new worksheets are added, the oldest ones are removed
• The workbook tracks outbound sales calls made by call center agents
• Date stamps appear in Column F with corresponding call notes appearing in Column G.
• The worksheets must be maintained seperately within the workbook. Merger into a master sheet is not an option at this time.
• The names of the worksheets are not standardized at this time, BUT they could be as necessary to accomplish my task.

Here is my question:

Is it possible to count the occurrence of a specific date value (column f) WITH a corresponding call notation value (column g). I want the formula or VBA code to do the following: Search every worksheet in the workbook and count the number of time 01/07 appears in column f next to "LVM" in column g on the same row.

Please tell me how to accomplish this. I am not trying to be lazy but have searched high and low on VBA, Pivot Tables, CountIF and CountIFS and am not connecting the dots. I'm a newbie by my own confession. Thanks in advance.

 

[StephenCrump]

Welcome to the Forum!

Try this code, which prints a summary starting at A1 in ActiveSheet. The code assumes "01/07" is text. If recorded as dates, simply check that DAY() is 1 and MONTH() is 7. (I'm using Australian not U.S. date notation)

Sub CountOccurrences()

    Dim lShtCount As Long
    Dim sht As Worksheet
    Dim sShtNames() As String
    Dim lShtTotal() As Long, lGrandTotal As Long
    Dim lShtIndex As Long
    Dim rng As Range
    Dim r As Long
    
    lShtCount = ActiveWorkbook.Worksheets.Count
    ReDim sShtNames(1 To lShtCount, 1 To 1)
    ReDim lShtTotal(1 To lShtCount + 1, 1 To 1)
    
    For Each sht In ActiveWorkbook.Worksheets
        lShtIndex = sht.Index
        sShtNames(lShtIndex, 1) = sht.Name
        Set rng = Intersect(sht.UsedRange, sht.Columns("F:G"))
        If Not rng Is Nothing Then
            For r = 1 To rng.Rows.Count
                If rng(r, 1) = "01/07" And rng(r, 2) = "LVM" Then lShtTotal(lShtIndex, 1) = lShtTotal(lShtIndex, 1) + 1
            Next r
        End If
        lGrandTotal = lGrandTotal + lShtTotal(lShtIndex, 1)
    Next sht
    
    [A1].Resize(lShtCount) = sShtNames
    [B1].Resize(lShtCount) = lShtTotal
    [B1].Offset(lShtCount) = lGrandTotal

End Sub

 

[StephenCrump]

Sorry, forget to mention that it will be very simple to generalise this, i.e. to define a function that you can call from Excel or from VBA to return the total count, e.g.

= MySpecialCount("01/07","LVM") or

= MySpecialCount(1,8,"SomeOtherText").