Loop through a folder in the computer

Put this into your workbook, and save it in the same folder as your other files.

This assumes your table is set up with 7 columns of value - the first for the file name and six for data: red, blue, yellow from column A, then from column B.

Sub LoopThroughFiles()
    Dim strPath As String
    Dim strWFile As String
    Dim wkbkWF As Workbook
    Dim wkSht As Worksheet
    Dim i As Integer
    Dim iRed As Integer
    Dim iBlue As Integer
    Dim iYellow As Integer
    Dim iRow As Integer
    
    iRow = 2
    
    Application.DisplayAlerts = False
       
    strPath = ThisWorkbook.Path & "\"
    strWFile = Dir(strPath & "*.xlsx")
    
    Do While strWFile <> ""
        If strWFile <> ThisWorkbook.Name Then
            Set wkbkWF = Workbooks.Open(strPath & strWFile)
            Set wkSht = wkbkWF.Worksheets("abc")
            ThisWorkbook.Worksheets("Results").Range("Table").Cells(iRow, 1).Value = strWFile
            For i = 1 To 2
                iRed = Application.CountIf(wkSht.Columns(i), "red")
                iBlue = Application.CountIf(wkSht.Columns(i), "blue")
                iYellow = Application.CountIf(wkSht.Columns(i), "yellow")
                'Do something with iRed, iBlue, and iYellow
                With ThisWorkbook.Worksheets("Results").Range("Table")
                    .Cells(iRow, 3 * i - 2).Value = iRed
                    .Cells(iRow, 3 * i - 1).Value = iBlue
                    .Cells(iRow, 3 * i).Value = iYellow
                End With
                iRow = iRow + 1
            Next i
            
            wkbkWF.Close False
        End If
        strWFile = Dir()
    Loop
    
End Sub