Automated Summary Sheet

I work for a construction company and we are trying to transfer our job descoping process from a paper process to an electronic process. The basis of the process is that you have a series of checklists that you go through for each job; on each checklist is a series of items that could be in the contract. For each item there are three possible outcomes: we are responsible, a subcontractor is responsible, or it’s not in the contract. The idea is to create a workbook in excel that would have the entire series of checklists in it, then the user would fill the checklists out and the workbook would create one summary sheet that would be auto populated. I am an intermediate user of excel at best, but I believe this is something that could be done with a macro of some sort. I have attached a spreadsheet of two of the checklists, and what I am hoping the summary sheet will look like.