Sub Macro1()
Dim ws As Worksheet
Dim wb2 As Workbook
Dim ws2 As Worksheet
Dim iEnd As Integer
Dim iEnd2 As Integer
Dim str1 As String

'copy cols C, G & I from 5 wkbks to this one
Set ws = ThisWorkbook.Sheets("data_from_files")
For iCt = 1 To 5
Set wb2 = Workbooks.Open("c:\Temp\Book" & iCt & ".xls")
Set ws2 = wb2.Sheets("Sheet1")
iEnd2 = ws2.Range("C1").End(xlDown).Row
iEnd = 1 + ws.Range("A65536").End(xlUp).Row
ws2.Range("C2:C" & iEnd2).Copy ws.Range("A" & iEnd)
ws2.Range("G2:G" & iEnd2).Copy ws.Range("B" & iEnd)
ws2.Range("I2:I" & iEnd2).Copy ws.Range("C" & iEnd)
wb2.Close
Next iCt

'do advanced filter on copied data
iEnd = ws.Range("A65536").End(xlUp).Row
ws.Range("A1:C" & iEnd).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("E1"), Unique:=True
iEnd2 = ws.Range("E65536").End(xlUp).Row

'formula to count occurrences
str1 = "--(R2C[-7]:R" & iEnd & "C[-7]=RC[-3])," & _
"--(R2C[-6]:R" & iEnd & "C[-6]=RC[-2])," & _
"--(R2C[-5]:R" & iEnd & "C[-5]=RC[-1]))"
ws.Range("H2").FormulaR1C1 = "=SUMPRODUCT(" & str1
ws.Range("H2").Copy ws.Range("H3:H" & iEnd2)
End Sub

Hth,
Merjet