Thread: Count data from multiple files

Here are the test files!

Thanks for the help.

Excel has about 65,000 rows.

Just a few files are equal to this amount, what about 12 months times 22 files each month ?

Well I am looking for vba solution because the whole report and statistical calculations include much more then count number of inquiries.

I was hoping that with the techniques of this simple report I would be able to build more complex report.

That is why what I really need is vba solution to count number of inquiries by person. Once I know how to do this I will be able to do everything else.

What you suggest is a good idea and I can use it in the future, but now I need the VBA solution.

Thanks a lot for helping!

Leith,

You are the best!

I think this will work, but will test it with the real data once you go to work.

Thanks so much! Be back with feedback in a few days.

Bob

Leith,

Testing the macro I have noticed that opening the first file everything is Ok. The macro is calculating everything right. With the second file it is not adding the count to the first one, but deleting it.

What I need is to sum the count from each month.

I was thinking to use arrays to store the values from each file opened and then sum the array and print the result to a cell.

Do you think that this will be a good approach?

Yes, this works.

Thanks, I will check if everything else is ok.

Be back with a feedback.

Cheers!

How I can add third criteria in the count if function?
Column M holds Status: "OK" or "Not Completed"
I have tried to change the code to this but is not working. Can I change the
Count if function to Evaluate("Sumproduct"...) so I can add more criteria?

Sub CountCalls()

  Dim CalcWks As Worksheet
  Dim FileName As String
  Dim FilePath As String
  Dim Person As Variant
  Dim Month As Integer
  Dim NameList As Range
  Dim NameRng As Range
  Dim RngEnd As Range
  Dim StatusRng As Range
  
   'Change the folder to where your files are stored
    FilePath = "D:\Documents\EXCEL VBA\TESTING"
    
    Set CalcWks = ThisWorkbook.Worksheets("Sheet1")
    
    Set NameRng = CalcWks.Range("A3")
    Set RngEnd = CalcWks.Cells(Rows.Count, NameRng.Column).End(xlUp)
    Set NameRng = IIf(RngEnd.Row > NameRng.Row, CalcWks.Range(NameRng, RngEnd), NameRng)
  
    Set NameList = NameRng
    
      Application.ScreenUpdating = False
      FileName = Dir(FilePath & "\")
      
        Do While FileName <> ""
          
          If FileName Like "*_########.xls" Then
            Set Wkb = Workbooks.Open(FilePath & "\" & FileName)
            Month = Val(Mid(Wkb.Name, 14, 2))
      
            Set NameRng = Wkb.Worksheets("Sheet1").Range("L2")
            Set RngEnd = NameRng.Parent.Cells(Rows.Count, NameRng.Column).End(xlUp)
            Set NameRng = IIf(RngEnd.Row > NameRng.Row, NameRng.Parent.Range(NameRng, RngEnd), NameRng)
            Set StatusRng = NameRng.Offset(0, 1)

             For Each Person In NameList
             Person.Offset(0, Month) = Person.Offset(0, Month) + _
                            WorksheetFunction.CountIf(NameRng, Person, StatusRng = "OK")
            Next Person
           
            Wkb.Close False
          End If
          FileName = Dir()
          
        Loop
            
      Application.ScreenUpdating = True
      
End Sub

I have tried to change the countif function with Evaluate.
When I use ""Peter"" in the formula everything is ok, but when change it to " & Person & "
I am getting an error.

What is wrong?

Sub CountCalls()

  Dim CalcWks As Worksheet
  Dim FileName As String
  Dim FilePath As String
  Dim Person As Variant
  Dim Month As Integer
  Dim NameList As Range
  Dim NameRng As Range
  Dim RngEnd As Range
  
   'Change the folder to where your files are stored
    FilePath = "D:\Documents\EXCEL VBA\TESTING"
    
    Set CalcWks = ThisWorkbook.Worksheets("Sheet1")
    
    Set NameRng = CalcWks.Range("A3")
    Set RngEnd = CalcWks.Cells(Rows.Count, NameRng.Column).End(xlUp)
    Set NameRng = IIf(RngEnd.Row > NameRng.Row, CalcWks.Range(NameRng, RngEnd), NameRng)
  
    Set NameList = NameRng
    
      Application.ScreenUpdating = False
      FileName = Dir(FilePath & "\")
      
        Do While FileName <> ""
          
          If FileName Like "*_########.xls" Then
            Set Wkb = Workbooks.Open(FilePath & "\" & FileName)
            Month = Val(Mid(Wkb.Name, 14, 2))
      
            Set NameRng = Wkb.Worksheets("Sheet1").Range("L2")
            Set RngEnd = NameRng.Parent.Cells(Rows.Count, NameRng.Column).End(xlUp)
            Set NameRng = IIf(RngEnd.Row > NameRng.Row, NameRng.Parent.Range(NameRng, RngEnd), NameRng)
      
            For Each Person In NameList
               Person.Offset(0, Month) = Application.Evaluate("SUMPRODUCT(--(L1:L100=" & Person & "),--(M1:M100=""OK""))")
            Next Person
           
            Wkb.Close False
          End If
          FileName = Dir()
          
        Loop
            
      Application.ScreenUpdating = True
      
End Sub