Excel Formula Help

Question

Hello All,

I was just wondering whether anybody would be able to help me with a formula problem I've been struggling with for the best part of a day now.

Pretty much, I want to calculate a pass rate for a performance tracker as I work within the engineering sector in Quality. We have 3 different areas where parts can pass and fail a Quality Inspection. I have managed to produce an overall pass rate with ease, but when it comes to creating a pass and fail percentage for select sectors, I am experiencing issues. As there are no numerical values in this spreadsheet, it seems as though the formula cannot understand that I wish to have a specific group in the whole table rather than the whole table being put into this percentage. Below is a table to sort of help explain what I need.

I want to be able to have a pass percentage of just the Weld criteria, rather than have it count all of the Weld passes and divide it by all of the Inspection results, I only want it to count it against the Weld inspection results.

Weld	Pass
Weld	Fail
Press	Fail
Weld	Fail
Press	Pass
Weld	Weld

Apologies for the poor explanation but this is getting rather frustrating and im in dire need of some help!

Any help is extremely appreciated!

Answer 1

Always easier to work with structured tables, but if you can't or don't want to you can replace a reference like Table1[Area] by D4:D9 or D4:D1000 if you're not sure where the range may end. Though, the structure of the formula is the same.

Otherwise, something like this would also work:

=SUM((D4:D20=G4)*(E4:E20="Pass"))/SUM(--(D4:D20=G4))

Answer 2

I've just given it a go and it works a treat, thankyou so much for your help mate!

Answer 3

My spreadsheet isn't an actual table, rather just a table I have created myself by colour coding etc, would this formula still work? I've tried putting a screenshot of my work but sadly it gives an error message when I try to upload it.

Answer 4

Perhaps like this?

=COUNTIFS(Table1[Area],G4,Table1[Result],"Pass")/COUNTIF(Table1[Area],G4)