Pivot table works perfectly, but I am working to a colleagues brief and the formula will fit in with their process a bit better.

It was the COUNTIF bit that I was missing, thank you very much.