Sum the data between two data column

[tta013]

Hi all,

Hope you are all well. I have a slight challenge.
Basically I have a sheet (Unit) with following which show how many units we have in a given date range.

Product, Start Date, End Date, Unit
A 01/02/2012 06/02/2012 3
A 31/01/2012 04/02/2012 3
B 03/02/2012 18/02/2012 3
A 05/02/2012 11/02/2012 3
A 01/02/2012 03/02/2012 3

On the second sheet,
I have a row with all the dates

Product 01/02/2012 02/02/2012 03/02/2012 04/02/2012 05/02/2012
A
What I would like to to have a unit sum for each date for each Product so that I know how many units i have processed each day. So Ideally , the data would show something like
01/02/2012 02/02/2012 03/02/2012 04/02/2012 05/02/2012 06/02/2012
A 9 9 9 6 6 6
B 3 3 3 3 3 3
------
I tried doing like below but in vain.
=SUMPRODUCT(--(Units!$B$2:$B$1000=$A7),--(Units!$D$2:$D$1000=BP$3),--(BP$3<Units!$E$2:$E$1000)*(Units!$G$2:$G$1000))

Products - $B$2:$B$1000
Start Date - $D$2:$D$1000
End Date - $E$2:$E$1000
Unit - $G$2:$G$1000

I am just wondering if a guru out there can help please?

Thanks

 

[Comfy]

This would not give accurate results based on your input data.

You have 3 Product A for 01/02/2012 to 06/02/12

There a loads of possibilities.

All 3 on 01/02/2012
2 on 01/02/2012 and 1 on 04/02/2012
1 on 02, 04, 06/12/2012

 

[anandvarma]

TRY THIS

Excel Workbook
	A	B	C	D	E	F	G	H
1	A	01/02/2012	06/02/2012	3
2	A	31/01/2012	04/02/2012	3
3	B	03/02/2012	18/02/2012	3
4	A	05/02/2012	11/02/2012	3
5	A	01/02/2012	03/02/2012	3
6
7
8		01/02/2012	02/02/2012	03/02/2012	04/02/2012	04/02/2012	05/02/2012	06/02/2012
9	A	9	3	3	3	3	3	0
10	B	3	3	3	0	0	0	0
Sheet1

 

[tta013]

Hi there,

Thanks for your replies.
=SUMPRODUCT(--($B$1:$B$5>=B$8)*($A$1:$A$5=$A9)*($D$1:$D$5)) wouldn't give the correct.

We need to check columns C to make sure the date we are checking is in BETWEEN.

Thanks