How to work out fomula with SUMIFS + Index&Function?

Dear Gurus,

My boss asked me to summarize our departmental audit report scoring into individual audit report and then breakdown by sections / questions / property / quarter.

I've tabulated all the data into raw data sheet and then tried using the formulas of sumifs and index&match, but i couldn’t get it work as it involves different scenario and I'm not this good with excel

Audit Data Template.xlsx

Let me try to explain a little more about this report:

1. First sheet is Auditor name MCE, this auditor will audit us multiple times of each questions listed as "P1, P2 and so on. The auditor will audit 3 different areas (Hotel Reservations, show ticket sales and in-house guest service) each area has their unique set of questions.
2. Second sheet is Auditor name LRA, this auditor will audit us 1 time on each question. They will also audit us 3 areas (Hotel Reservation, Telephone Procedure, Request of Service)
3. Third sheet is auditor name Forbes A1, they will audit us 1 time on each question. This auditor audit only 2 areas (in-house guest service and F&B reservation service).
4. Fourth sheet is auditor Forbes C1. They audit us 1 time on each question. Areas audited are 2 areas (Hotel Reservation, in-house guest service).
5. MCE, LRA and Forbes Auditor audit the property call C1 in the raw data sheet and Forbes A1 audits the property call A1

My ultimate goal in this template is to count the number of YES / NO / N/A of cross referencing the question based on Auditor / Areas / on quarterly basis

Hope anyone could show me some lights here. Much appreaciated !

Thank you in advance.

BR,
EC

Hi FDIbbins,

I've done some manual tabulation and is colored in orange all of the sheets. That's the end result that I'm looking.

TQ

Originally Posted by FDibbins

What would your answer look like? Can you provide a few samples (manually calc'd if needed)

Perhaps something like this? Using Array Formula, you need to press CTRL-SHIFT-ENTER button together, Im givin you example just for Hotel Reservation...

Azumi,

Exactly! thanks alot !! Will look up the Array formula in details so that i know what you've written

Thanks everyone !

Originally Posted by azumi

Perhaps something like this? Using Array Formula, you need to press CTRL-SHIFT-ENTER button together, Im givin you example just for Hotel Reservation...