I am analyzing student achievement data to determine if the students failing courses in the third marking period are the same students that failed classes in the first and second marking periods. I identify the students by their student ID number, which is unique to each student. Each marking period has its own workbook (separate files but the sheets are identical) and the table for each marking period is named AggregateData1, AggregateData2, etc with the number indicating the marking period the data was drawn from.

Example: File Name MP1 has table AggregateData1 File Name MP2 has table AggregateData2

I need help creating a formula that will compare student ID numbers across the marking periods. It should check the column Student ID and make sure that it appears in each table and that the value in column '# failed' is >=1 every time. The number of times a unique ID appears in previous workbooks with the corresponding value for '# failed' should be counted and returned.

I hope that makes sense and thanks for the help in advance.