Bradford Factor Dilemma

[mistersteve]

Hello all.

I am wondering if anyone can solve my problem regarding the Bradford Factor.
I record in the "Planner Page" Sick/Absence "S" as in BF1, and is shown in the "Stats Page" BF2

My issue is with the Occurrence part.
I decided I needed to amend the formula to ignore Sat and Sun.
But then I realised if "S" is recorded on Fri, Sat, Sun, Mon, and the formula and Sat and Sun is ignored the the Occurrence will be 2, but actually it should be 1.
Therefore, I need the formula to ignore Sat and Su if they dont have a Weekday with them, but consider them if a weekday is part of the "S".
Currently the formula is:
=SUMPRODUCT((Planner!F14:CR14="S")*(Planner!G14:CS14<>"S"))+SUMPRODUCT((Planner!F34:CR34="S")*(Planner!G34:CS34<>"S"))-(IF(AO36=2,AO36-1))+SUMPRODUCT((Planner!F54:CS54="S")*(Planner!G54:CT54<>"S"))-(IF(AP36=2,AP36-1))+SUMPRODUCT((Planner!F74:CR74="S")*(Planner!G74:CS74<>"S"))-(IF(AQ36=2,AQ36-1))

I hope this all made sense, as i found it difficult to explain, and thank you in advance.