Sum if between a date range

just1joe

Board Regular
Joined
Apr 15, 2003
Messages
79
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
RSTUVWXY
12AvailabilityTagShippedTagUtilizedShipOutShipModeShipBackShipMode
13300043212/28/06Ground1/11/2007Ground
143000   
153000   
163000237601/01/07Ground1/14/2007Overnight
17300086412/28/06International1/22/2007International
183000   
19300075601/11/07Overnight1/24/20073Day
203000   
21300054001/15/07Overnight1/23/20073Day
223000   
233000   
24300043201/11/07International2/5/2007International
253000   
26300021601/19/07Ground2/1/2007Ground
2007 Q1
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Board Regular
Joined
Apr 15, 2003
Messages
79
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,685
Members
410,697
Latest member
srishtijain0708
Top