Sumif based on a text range, text criteria and and text sum range

Hi.

I have a sheet with some financial ratios which have either been 'passed' or 'failed'. These are for various sites, each of which is in one of three states. I basically want to find the average pass rate/fail rate for each state. So my final product would be a table, with a three states in the column, then the headings for the rest of the table columns would be XYZ ratio, ABC ratio, 123 ratio etc. The table will then have the percent of passes or fails for that ratio, in that state.

The issue I face is for the pass/fail situation, there is also an 'N/A' or 'Div/0' which I would want omit when I calculate the overall average of pass or fails.

See attached sheet for an idea of what it's like.

Any ideas?

Cheers