Here is my current formula
=SUMPRODUCT((Data!J$2:J$64997=Laurel!A30)*(Data!B$2:B$64997=Laurel!B$20)*(Data!E$2:E$64997=Laurel!C$20)*(Data!H$2:H$64997))/SUMPRODUCT((Data!J$2:J$64997=Laurel!A30)*(Data!B$2:B$64997=Laurel!B$20)*(Data!E$2:E$64997=Laurel!C$20)*(Data!F$2:F$64997))*100
I am basically looking up a week/plant/type and getting the pounds shipped divided by week/plant/type and getting the capacity shipped to get the % of utilization.
I have this formula 75 times on 13 worksheets.
Any way to get this so the formula is faster and more reliable?
=SUMPRODUCT((Data!J$2:J$64997=Laurel!A30)*(Data!B$2:B$64997=Laurel!B$20)*(Data!E$2:E$64997=Laurel!C$20)*(Data!H$2:H$64997))/SUMPRODUCT((Data!J$2:J$64997=Laurel!A30)*(Data!B$2:B$64997=Laurel!B$20)*(Data!E$2:E$64997=Laurel!C$20)*(Data!F$2:F$64997))*100
I am basically looking up a week/plant/type and getting the pounds shipped divided by week/plant/type and getting the capacity shipped to get the % of utilization.
I have this formula 75 times on 13 worksheets.
Any way to get this so the formula is faster and more reliable?