SUM of Shift-wise and of both the Shifts

Dear Excel Experts,
I have a list of contractors, day, shift-wise movement i..e Performed TEUs and with using IF and SUMIFS I have to calculate Performed TEUs upon selection of..

Specific contractor or all contractors, from shift and to shift, Specific Shift or Both the Shifts Performed TEUs; it's done for all combinations excluding Both the shifts.

For example
Case 1
Contractor : Casby Logistics
From Date : 30-Apr-18
To Date : 02-May-18
Shift : Day
Performed TEUs : 5229

Case 2
Contractor : All TT Contractors
From Date : 30-Apr-18
To Date : 02-May-18
Shift : Night
Performed TEUs : 10459


Case 3
Contractor : Priti Logistics

From Date : 30-Apr-18
To Date : 02-May-18
Shift : Day
Performed TEUs : 5229

Case 4
Contractor : All TT Contractors
From Date : 30-Apr-18
To Date : 02-May-18
Shift : Both
Performed TEUs : 17390

As per formula in L2 cell, all combinations are working excluding Both Shifts., kindly help to resolve the same.

Thanks in advance.

Kravindra

Hi xladept,

Thanks for your prompt response and great code script,

TEUs should be change after changing following columns..
Contractor | From Date | To Date | Shift


As per macro defined by you, it is considering only Contractor , for calculating TEUs remaining criteria are to be caonsidred| From Date | To Date | Shift


In case of All TT Contractors : TEUs are to be calculated on account of all 4 contractors ie. Casby Logistics, Priti Logistics, Royal Translines, Sudin Enterprises

In case of Both Shift: TEUs are to be calculated on account of both the Shifts i.e. Day & Night

Kindly relook and revert.

Kravindra

Thanks in advance for your code.

Fantastic !! Hat's off to you Boss!
You are really Charm !!
Thanks your great work.

Kravindra