Sumproduct for multiple tables

[Daw12]

Hi All,

I require a formula to Sum the total amount of parts produced within a certain date range for the specified product. My problem is that the data range spans what effectively is multiple tables.
In the example below I can easily do this for a single shift using a Sumproduct formula, but how can I make the formula work so it searches over multiple days/shifts?

=SUMPRODUCT(((S4<=D1:E1)*(T4>=D1:E1))*(D6:E8=N4)*E6:E8)

Any help would be greatly appreciated, I have spent a few days looking for a solution but to no avail.

Thanks

 

[Crystalyzer]

Couldn't you just add two more sumproduct formulas to cell P4 as follows:

=SUMPRODUCT(((S4<=D1:E1)*(T4>=D1:E1))*(D6:D8=N4)*E6:E8)+SUMPRODUCT(((S4<=G1:H1)*(T4>=G1:H1))*(G6:G8=N4)*H6:H8)+SUMPRODUCT(((S4<=J1:K1)*(T4>=J1:K1))*(J6:J8=N4)*K6:K8)

 

[Daw12]

Unfortunately I can't, as in the real spreadsheet this would be done for entire year. The above is just a small portion of the spreadsheet where I am testing.

 

[Daw12]

After a lot of trial and error I have managed to find a formula that works.

{=SUM(IFERROR(((S4<=D1:K1)*(T4>=D1:K1))*(D6:J8=N4)*E6:K8,0))}