Finding values based on multiple conditions

Hi Guys,
I need help with formulas to create data for audits. My data relates to vehicle pass and fail rates at annual inspection. The government stats only take into account the first time a vehicle is presented for inspection first time and not when its retested. I've managed to find a formula to calculate the number of tests from my list using this formula
=ArrayFormula(SUM(IF(FREQUENCY(IF(LEN(B2:B200)>0,MATCH(B2:B200,B2:B200,0),""),IF(LEN(B2:B200)>0,MATCH(B2:B200,B2:B200,0),""))>0,1)))

My problem is that i now have to count the pass and fail rate but only on the data that is found in the formula above. I hope this makes sense.

Thanks guys!

Hi and welcome to the forum

Thanks for the title chage

Also, Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

wow that really IS a pcture,,,from your cell phone?

I would probably add a helper column and use a countifS() to ID the trailor passes, then use index/match with a MAX() to ID the data you wat, but its hard to say for sure. As I said above, please uplad a sample workbook, not a pic

Hi,

My machine advises me against opening your attachment as it contains 'unreadable content' - can you please double-check?

Regards

Ok, thanks. I can open that one.

So what are your desired results, why, and in which cells should they be entered?

Regards

Please bear with me because this could get confusing. Column A is just a date column. Column B is a list of all Vehicles/trailers that have been taken to a MOT station. Government stats are only calculated by the first time they are taken to the test station not when they are taken back if they fail the first test. This data is calculated into G5 using the formula that i had in my first post and works ok. But the passes and fails (column D) are also calculated in the same way, only the first test is taken into account to give the percentages for inital and final pass rate.

I was just using =countif(C2:200,"pass") but this obviously will count every cell and i need only unique cells counted.
Hope this makes sense