I am trying to calculate absent percentages using sumproduct and while I can use the formula on its own, I have been trying all sorts but with no luck to get correct syntax for results and unsure when trying to divide, multiply with different criteria. Google confused me further.
This works fine on its own and column C is the total possible hours
=SUMPRODUCT(--(AbsData!E2:E200000=Medicine!C7),--(AbsData!F2:F200000=Medicine!D7),AbsData!C2:C200000)
I am trying to divide the same criteria but with lost hours which is in column J
SUMPRODUCT(--(AbsData!E2:E200000=Medicine!C7),--(AbsData!F2:F200000=Medicine!D7),AbsData!J2:J200000)
Below is one example of where I have been trying with all sorts but I'm lost as to where I am going wrong and would appreciate any help to point me in the right direction
=SUMPRODUCT(--(AbsData!E2:E200000=Medicine!C7),--(AbsData!F2:F200000=Medicine!D7),AbsData!C2:C200000))/SUMPRODUCT(--(AbsData!E2:E200000=Medicine!C7),--(AbsData!F2:F200000=Medicine!D7),AbsData!J2:J200000))
kind regards
Ian
This works fine on its own and column C is the total possible hours
=SUMPRODUCT(--(AbsData!E2:E200000=Medicine!C7),--(AbsData!F2:F200000=Medicine!D7),AbsData!C2:C200000)
I am trying to divide the same criteria but with lost hours which is in column J
SUMPRODUCT(--(AbsData!E2:E200000=Medicine!C7),--(AbsData!F2:F200000=Medicine!D7),AbsData!J2:J200000)
Below is one example of where I have been trying with all sorts but I'm lost as to where I am going wrong and would appreciate any help to point me in the right direction
=SUMPRODUCT(--(AbsData!E2:E200000=Medicine!C7),--(AbsData!F2:F200000=Medicine!D7),AbsData!C2:C200000))/SUMPRODUCT(--(AbsData!E2:E200000=Medicine!C7),--(AbsData!F2:F200000=Medicine!D7),AbsData!J2:J200000))
kind regards
Ian