sorry, I've realised my mistake... I forgot to change the values for the D column!
=SUMPRODUCT(($B$3:$B$999=B4)*($D$3:$D$999="fail"))