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

epaprhasguy80

New Member
Joined
Jan 7, 2014
Messages
2
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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)

Code:
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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").
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top