Multiple COUNTIF and SUMIF criteria

Hello, I have been trying to solve this for a few hours but to no luck. Any assistance would be greatly appreciated.

I've attached a simplified example worksheet of what I am trying to achieve so I can apply this to my larger main spreadsheet. I have just manually inserted the data in the 'Stats' table where the formulas should be.

Thing that need to be considered:

The names in the 'Options' table, that links to the data validation of the 'Projects Data' for the main spreadsheet will be susceptible to change, so in the seach criteria for COUNTIF/SUMIF can't be "PM3", needs to be the cell reference.

Completed projects are not to be counted in the 'Stats' table. (This is where it started going wrong for me)

Optionially is it possible to not count PM/QS's if they are either left blank or have NA typed in the cell.

I hope the example and info below is sufficient, please do ask if you need further information.

Let's just go for the easy ones:

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

You might need to sum the values for the left hand side.

Mmm. TMS's looks simpler than mine:

=SUMPRODUCT((LEFT($F$4:$F$11,2)="PM")*(LEFT($G$4:$G$11,2)="QS")*($E$4:$E$11=J5))

to count and

=SUMPRODUCT((LEFT($F$4:$F$11,2)="PM")*(LEFT($G$4:$G$11,2)="QS")*($E$4:$E$11=J5)*$H$4:$H$11)

to sum, with a cut down version for the QS's only. see sheet.

Thanks for the rep.

You're welcome!