Sum if between a date range

[just1joe]

I am attempting to track number of items (Tags) utilized between a date range. V13 shows the date the number of Tags in T13 will be shipped out and X13 shows when they will be back. I need to show in U13 the total number of tags in column T which have dates in column V and X which overlap the dates in V13 and X13. A SumProduct does not seem to acheive this and I'm not sure f there is another method.

Any help would be appreciated.

- Joe

Opportunities Calendar 20061011 1200.xls
	R	S	T	U	V	W	X	Y
12	Availability		TagShipped	TagUtilized	ShipOut	ShipMode	ShipBack	ShipMode
13	3000		432		12/28/06	Ground	1/11/2007	Ground
14	3000
15	3000
16	3000		2376		01/01/07	Ground	1/14/2007	Overnight
17	3000		864		12/28/06	International	1/22/2007	International
18	3000
19	3000		756		01/11/07	Overnight	1/24/2007	3Day
20	3000
21	3000		540		01/15/07	Overnight	1/23/2007	3Day
22	3000
23	3000
24	3000		432		01/11/07	International	2/5/2007	International
25	3000
26	3000		216		01/19/07	Ground	2/1/2007	Ground
2007 Q1

 

[acw]

Joe

Not sure I follow what you are chasing, but does

=SUMPRODUCT(--(V13:V26>=V13),--(X13:X26<=X13),(T13:T26))

in U13 give you the correct result?


Tony

 

[just1joe]

Thanks Tony,

That will work only for rows where the dates in column V and X are between the dates for V13 and X13.

Your solution would provide the result of 432 for cell U13. What I am looking for would be 3672 in U13 beacuse the dates for rows 13, 16 and 17 overlap the dates in V13 and U13.

U16: 4428
U17: 5400

- Joe

 

[barry houdini]

Not quite sure it would give you those figures for U16 and U17 but try

=SUMPRODUCT(1-(X$13:X$26<=V13),1-(V$13:V$26>=X13),T$13:T$26)

in U13 copied down