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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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
 

Forum statistics

Threads
1,141,667
Messages
5,707,719
Members
421,525
Latest member
carian1980

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top