Sumproduct with multiple date criteria

Having a tough time with this one.

Sheet 1
Column A = Start Date, Column B = End Date, Column C = Quantity.

Sheet 2
Row A = Start Date, Row B = End Date.
I would like Row C to sum quantity from sheet 1 where ever the two date 
ranges intersect.
The date ranges on sheet 2 represent the beginning and ending of a week 
(Mon-Sun).

Sheet 1
Column A   Column B   Column C
01JAN2010  24JAN2010  1,000

Sheet 2
Row A	04JAN2010  11JAN2010  18JAN2010  25JAN2010
Row B	10JAN2010  17JAN2010  24JAN2010  31JAN2010
Row c	1,000          1,000          1,000         0

0
Utf
5/27/2010 9:03:41 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
914 Views

Similar Articles

[PageSpeed] 35

Do the Sheet 2 Start and End Dates both have to lie within the Sheet 1 Start 
and End Dates?

Regards,

Tom


"StonyfieldRob" wrote:

> Having a tough time with this one.
> 
> Sheet 1
> Column A = Start Date, Column B = End Date, Column C = Quantity.
> 
> Sheet 2
> Row A = Start Date, Row B = End Date.
> I would like Row C to sum quantity from sheet 1 where ever the two date 
> ranges intersect.
> The date ranges on sheet 2 represent the beginning and ending of a week 
> (Mon-Sun).
> 
> Sheet 1
> Column A   Column B   Column C
> 01JAN2010  24JAN2010  1,000
> 
> Sheet 2
> Row A	04JAN2010  11JAN2010  18JAN2010  25JAN2010
> Row B	10JAN2010  17JAN2010  24JAN2010  31JAN2010
> Row c	1,000          1,000          1,000         0
> 
0
Utf
5/28/2010 10:24:03 AM
Reply:

Similar Artilces:

How can I merge multiple customizations?
I have different customizations installed in different workstations. We want to move from individual GP installations to a central Citrix installation. How can I merge the different customizations that user have and provide them all through a central GP installation on citrix? -- Hector Herrera Business Systems Analyst II Northwestern Medical Faculty Foundation Chicago, IL USA Install all the customizations on the Citrix client. Every user logging in via Citrix will have access to them. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your gptip42toda...

Subquery to find consecutive dates
Howdy Folks, I have a table that contains a series of asbestos sampling dates at several different locations. A simplified version of the structure is as shown below: [Location] [Sampler] - Company that performed the sampling [Sample Date] [Result] - Numerical Result [Detection Flag] - Yes or No When the detection flag is "Y" for a given day, I want to know if the next day also has a detection. If not, I want to ignore that detection. If yes, I want the first day to get flagged with a "1" and the subsequent day to be flagged with a "2". This gets trickier when ...

Place X in cell if criteria met`
Is there a formula to do this? If cell B2 = pencils Put an "X" in cell B7 If cell B2 = pens Put an "X" in cell B8 If cell B2 = erasers Put an "X" in cell B9 Thanks in advance in cells B7 put =if(B2="pencils","x","") in Cell B8 put =if(B2="pens","x","") In cell B9 put =if(B2="erasers","x","") "jhicsupt" wrote: > Is there a formula to do this? > > If cell B2 = pencils > Put an "X" in cell B7 > > If cell B2 = pens ...

SOP Actual Ship Date
In SOP setup, we do not use a Separate Fulfillment process. So, I'm assuming the Actual Ship date defaults from either the system date or the order date on the date the order is created (because it's also fulfilled on that date). Is there any way that we can have the Actual Ship date default to the Invoice date at the time the order is transferred to an invoice? We're trying to get a SmartList that shows (by item) Order Date AND Actual Ship Date/Invoice Date. Is this possible some other way without SmartList Builder? Elaine, 1. Is there any way that we can h...

refer to data on multiple worksheets using hlookup/look up data on many worksheets?
Hi I have data on many worksheets in the same workbook. Is it possible to look up data on multiple worksheets using Hlookup, which means can the second argument in the formula refer to multiple sources? If it is not possible, is there any other way I can do so? I would like to select and display data according to a fixed order, whereby the data is located in one of many worksheets in the same workbook. For eg, I have stock returns of many firms in many worksheets. Say I would like to display in a single worksheet the returns of Firm D, Firm Z, Firm R; whereby the data of these 3 fir...

does EXCEL support multiple display monitors?
Hello All Excel Experts How do I move chart windows to multiple display montors?? (one computer runnng three monitors... Excel Charts will not move off the primary monitor. Your advice is EXTREMELY appreciated thanks. Denni Dennis, Excel certainly supports multiple monitors. I have Excel 2003 running on 3 19" flat panel displays (controlled by a Matrox Perihelia 256MB card). You can't move a chart object off the Excel window, so if you have Excel displayed in only one monitor, the chart is restricted to that monitor. However, if you have Excel stretched across all three monitors, ...

Multiple IF statements in one cell
Excel 2003 on XP. I have a project control listing that has, in part of it, nine columns that will hold the dates that certain stages are met, i.e. a date will be entered as each stage is met and the following stages will be empty, until Stage 9 - Project Complete/Signed Off. Stages 1-9 each have a unique Stage Name/defintion. I would like a following cell in the row to automatically show the Stage Name for the latest date in the corresponding stage cell for that project. I have a nested IF statement that works but as the project list is to get very large I would like a neater way of doin...

how do i count mails with time and date
i have a helpdesk , i would like to know the tracking all mail , as like time and date on responded "Bhanu C" <Bhanu C@discussions.microsoft.com> wrote in message news:4F0F08B1-3302-43F9-B2F8-5FE1BEC99A9B@microsoft.com... >i have a helpdesk , i would like to know the tracking all mail , as like >time > and date on responded Why not just look at the InfoBar? -- Brian Tillman [MVP-Outlook] see http://www.slipstick.com/exs/customfields.htm - specifically the replied time section. If needed, you can copy rows to excel and compare dates etc. -- Diane Poremsky...

SumIf
Hi =SUMIF(A6:A2000,"(left(a6:a2000,4))=(left($K$14,4))",F6:F2000) I want to create a sub total of all values in column F, at certain subtotal cells in column F where the the first 4 (or other to be set)characters of a code in cells a6 to a2000 match the first 4 characters in cell$K$14 (or other cell to be set). The above doesn't seem to work, is it possible? am I missing something? Any help would be greatly appreciated. Ritchi Try this array* formula: =SUM(IF(LEFT(A$6:A$2000,4)=LEFT($K$14,4),F$6:F$2000,0)) * As this is an array formula, then once you have typed it in (or s...

Counting number of rows based on mutiple criteria
Does anyone know how to count the number of rows based on mutiple criteria on other columns? For example I want to count the number row that meet the criteria of "Yes" in Col 2 and "Yes" in Col 3. The result would be 2 in the example below. I tried different combinations of Vlookup, Countif and Sumif and could not come up with anything that worked. Col 1 Col2 Col3 A Yes No B Yes Yes C No No D No Yes E Yes Yes Your help is greatly appreciated! Hi, =SUMPRODUCT((B1:B10=&qu...

Like criteria on a combo box ot working the way I want... Please help!
All, Below is a the standard code I use in a combo box. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[llListingID] = " & str(Nz(Me![cboTerritoryName], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark Me.Form.AllowAdditions = False The query used to find the data is... SELECT qryLister.llListingID, qryLister.llTerritoryName FROM qryLister WHERE (((qryLister.llTerritoryName) Like "*"+[Enter any part of Territory Name:]+"*")); All works fine for the first look-up. But in order to refresh the combo box so it allows one to &...

Formatting date fields after export
I am experiencing problems with my exported date fields into Excel from other applications. The data formats to "yyyy-mm-dd" and cannot be modified unless I double-click on each field. Has anyone else experienced this problem? And what solutions would you suggest? It is probably seen as text, select the imported dates, do data>text to columns, click next twice, under column data format select date and YMD click finish Regards, Peo Sjoblom "Raymond" wrote: > I am experiencing problems with my exported date fields into Excel from other > applications. The d...

Combined date time cell to separate date & time components #3
From file dump have combined date time cells eg 14/04/03 14:20 (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) Please look for the previous answers you've gotten, including at least 2 to your identical post yesterday. In article <F0A5B5BA-2E80-4875-A042-D3514FD4E228@microsoft.com>, "Ma...

Parse multiple text lines into 1 line in excel
help. I am an excel beginner and can't find out how to turn multipl lines of text into 1 row in excel. It's probably really easy but m manual is USELESS. Can anyone help ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com debbie You're a little short on details. If nothing below fits the bill post back. "Multiple lines" is how many and is each line in a separate cell down one column? Do you want all lines to go into one cell? You can use this form...

Multiple SMTP Address
If a user is set up to have multiple SMTP address setup in active directory. When they send an email in outlook how can they chose which one it is from. You can only do this, in the setup you describe, by using a 3rd party app such as ChooseFrom from www.ivasoft.biz -- Mark Arnold. "Shane" wrote: > If a user is set up to have multiple SMTP address setup in active directory. > When they send an email in outlook how can they chose which one it is from. On Tue, 31 May 2005 06:41:03 -0700, "Shane" <Shane@discussions.microsoft.com> wrote: >If a user is s...

Date format and ...
Hi all, please help me with: 1) I get the date format from Windows Control Panel (locale info) with 'GetLocaleInfo()', these formats are: dd/MM/yyyy dddd, dd' de 'MMMM' de 'yyyy But I have mi date in a COleDateTime object and COleDateTime::Format() does not work with these date formats, ¿How can I format my COleDateTime with dd/MM/yyyy and dddd, dd' de 'MMMM' de 'yyyy date formats? 2) Also, I have a CDateTimeCtrl and CDateTimeCtrl::SetFormat() works fine with dd/MM/yyyy and dddd, dd' de 'MMMM' de 'yyyy, but it does not work...

Multiple Outlook 2003 problems
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3EAFE.732D7EA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 1) every time it starts up I get a runtime error (one of the error = processing line number 67 errors) - It's a default install, nothing = special connecting to an Exchange server. 2) I get prompts every few minutes to login. Same two prompts every = time. One is a windows login prompt (to what I don't know, can only = assume something in outlook) and the other is a .net login. I always = just c...

Date query 08-03-07
Hi I'm trying to write a query that selects records based on a date range and Uses the Date of Birth if the Date entered is blank (null) or uses the date entered if that date is filled in. My query works fine for the first condition where I say if "date entered is null" and DOB between the 2 dates. My problem comes in when I put in the second line in the criteria and say if the date entered is between the 2 dates. Any help would be appreciated. Thanks Jeff ...

Multiplication problem- Please Help!
Hello all. I am a novice Excel user, and cannot seem to locate the source of my problem. Please help! Here is my sheet: C4 D4 E4 F4 G4 H4 9/22/2005 7:10 PM___10/1/2005 12:13 PM___ 8.7__52.6__6.0__317.64 Start date is entered into C4. End date is entered into D4. The following formula is in E4 to determine elapsed days: =IF(D4=0,"",IF(C4=0,"",(IF(ISERROR((D4-C4)*24),"",(D4-C4)*24)/24))) F4 contains "FLAGED HOURS" (for a body shop estimate) G4 contains a value u...

Multiple AND OR functions
Is it possible to make this function work? =IF(AND(AND($B9="Z",AE9=35),OR(J9=1,J9="M",J9="C")),1000,IF(AND(AND($B9="A+",AE9=35),OR(J9=1,J9="M",J9="C")),750)) I need to find out if Cell B9 = Z and if Cell AE9 = 35, if this true then check cell J9 and check if it equals 1, M or C then return the value as 1000. (that bit works OK) I also want it to check if an alternative statement is true if the first is false whereby it checks the the same set of cells but this time, check if B9 =A+, if Cell AE9 = 35, if this true then...

how to count if the value falls between a date range
example 1/1/2005 1/2/2005 = 2 2/1/2005 2/2/2005 = 2 I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and similarly 2/1/2005 and 2/3/2005 =countif(a1:a10,">="&date(2005,1,1)) - countif(a1:a10,">"&date(2005,1,3)) (I'm not sure what between means--include the end dates or not???) joe wrote: > > example > 1/1/2005 > 1/2/2005 = 2 > 2/1/2005 > 2/2/2005 = 2 > > I am trying to get the ount if the value is between 1/12005 and 1/3/2005 and > similarly 2/1/2005 and 2/3/2005 -- Dave Peterson Dave, ...

Sorting records based on a couple or many criteria items from a qu
I currently have a query set up consisting of several fields from several tables. I'd like to solicit advise on the best way to set up search criteria. I've looked into building a dialog box containing combo boxes where the source of the combo boxes are individual queries. This is exactly the type of mechanism I'm looking for, but according to the documentation I've read I can't have more than two or three of the combo boxes on the dialog box as it creates a complex query. I have about eleven different fields I'd like to have the option to sort on - ma...

Main form causing multiple errors
History: I have created a database for my company to track all incidents, equipment issues, and more. The database has been in use for over a year and has been working quite well. It is a split database with the back end located on a network hard drive and the front end updated through autofeupdate. The database is setup for a multiuser environment. I have integrated functions to send email messages and create reports daily. Recently, I have added six list boxes to the main form. Each listbox displays information on current records. For instance, one listbox shows all the open equipment ticket...

Can a variable (date) be used in a SUMIF formula?
I hoping that you can help me. Below you will find an example of a spreadsheet that I'm trying to to perform a sumif formula with dates. In the constant row I use the formula of =SUMIF($B$7:$B$11,">12/12/2002",$C$7:$C$11) and receive the value of 1,695.00. In the variable row I use the formula of =SUMIF($B$7:$B$11,">c3",$C$7:$C$11). I'm trying to acutally use the row of dates instead of having to put in the actual date each time but when I use the field of C3 for the date it returns the value of 0. A B C D ...

Parameter Query For Selected Dates or All Dates
Hi Folks - I'm having a senior moment. I have a query with a date field. I want to use Between [Start Date] and [End Date] to choose a date range, but if the user leaves the parameters blank, I want all records returned. I was able to do this when I passed a form control to the query, but I can't figure out how to do it within the criteria row of the query. Any help would be appreciated. Thanks. -- Michael On Wed, 12 Dec 2007 17:02:00 -0500, Michael wrote: > Hi Folks - I'm having a senior moment. I have a query with a date field. I > want to use Between [Start Da...