Current formula i am using:

=IF(P14=1,”6″,IF(P14=2,”8″,IF(P14=3,”10″,IF(P14=4,”12″,IF(P14=5,”14″,IF(P14>5,”14″))))))+IF(W14=1,”6″,IF(W14=2,”8″,IF(W14=3,”10″,IF(W14=4,”12″,IF(W14=5,”14″,IF(W14>5,”14″))))))+IF(AD14=1,”6″,IF(AD14=2,”8″,IF(AD14=3,”10″,IF(AD14=4,”12″,IF(AD14=5,”14″,IF(AD14>5,”14″))))))+IF(AK14=1,”6″,IF(AK14=2,”8″,IF(AK14=3,”10″,IF(AK14=4,”12″,IF(AK14=5,”14″,IF(AK14>5,”14″))))))+IF(AR14=1,”6″,IF(AR14=2,”8″,IF(AR14=3,”10″,IF(AR14=4,”12″,IF(AR14=5,”14″,IF(AR14>5,”14″))))))

Every time need to change the cell references

P14, W14, AD14, AK14, AR14, all refers to Weekend (Friday)

Need a short formula to sum weekend day (Friday) value (Trips) replace by (Overtime) if match. i created two named ranges Trips & Overtime

If a driver make 1 trip on friday 6 hrs OT 2 trips 8 hrs etc.

i have tried sumif sumproduct but unable to get a perfect one

By: Mohammed Ibrahim