 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008date > date function
hi, i'm trying to look in column A for a date that falls within a specific time frame, then look in column B to see if that date is > than the date in column A, if it is, then add the qty in column C. ex: A1: 5/22/08 B1: 5/23/08 C1: 4 A2: 5/23/08 B2: 5/23/08 C2: 2 so if formula is look in col A for all dates within 5/19 and 5/22, then i want the result to show 4. tia I'm sure I've seen your post before, but try this approach - put your start-period date in D1, your end-period date in E1 and this formula in F1: =SUMPRODUCT((A$1:A$100>=D$1)*(A$1:A$100>=E$1)*(B$1:B$100>A$1:A$100)*(C $1:C$100)) This checks to see if the dates in A fall within the start-period and end-period, and that the date in B is larger than A, and adds column C if those conditions are met. I've assumed you have 100 rows, so change this if you have more. Hope this helps. Pete > hi, i'm trying to look in column A for a date that falls within a specific > time frame, then look in column B to see if that date is > than the date in [quoted text clipped - 6 lines] > want the result to show 4. > tia i've done a variation of this. i actually need to add in a value and can't get the syntax correct... =SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100>DATE(2008,5,18))*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$100>C$3:C$100)*(E$3:E$100) i forgot, i need to base this calculation based on a field in column H. thanks again. Well, you've introduced things in columns D and E as well as H, so I'm not really sure what you are doing now. However, if you use the + symbol in this type of formula it will be taken as OR. So if your latest condition is for column C to be ABCD or ABC then you have missed a bracket after the -- as well as from the end of the formula. Also, your ranges were different for column H. I think it should be: =SUMPRODUCT(((H$3:H$100="ABC")+(H$3:H$100="ABCD"))*(C$3:C $100>DATE(2008,5,18))*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$100>C$3:C $100)*(E$3:E$100)) Hope this helps. Pete > i've done a variation of this. i actually need to add in a value and can't > get the syntax correct... [quoted text clipped - 3 lines] > i forgot, i need to base this calculation based on a field in column H. > thanks again. Did you mean <=E$1 rather than >=E$1 ? -- David Biddulph I'm sure I've seen your post before, but try this approach - put your start-period date in D1, your end-period date in E1 and this formula in F1: =SUMPRODUCT((A$1:A$100>=D$1)*(A$1:A$100>=E$1)*(B$1:B$100>A$1:A$100)*(C $1:C$100)) This checks to see if the dates in A fall within the start-period and end-period, and that the date in B is larger than A, and adds column C if those conditions are met. I've assumed you have 100 rows, so change this if you have more. Hope this helps. Pete On May 29, 2:53 pm, Ann <A...@discussions.microsoft.com> wrote: > hi, i'm trying to look in column A for a date that falls within a specific > time frame, then look in column B to see if that date is > than the date [quoted text clipped - 7 lines] > want the result to show 4. > tia Yeah, you're right, David - my mistake, though the OP seems to have got her answer from another post. Pete On May 29, 5:09 pm, "David Biddulph" <groups [at] biddulph.org.uk> wrote: > Did you mean <=E$1 rather than >=E$1 ? > -- [quoted text clipped - 31 lines] > > - Show quoted text - thanks guys, i've gotten the correct formula based on the below. now, here's a modification: when i add another variable in the beginning, i can't get the correct syntax... =SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100>DATE(2008,5,18))*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$100>C$3:C$100)*(E$3:E$100) slight modification, i want the sum the qty of what's in those rows based on the dates. > Yeah, you're right, David - my mistake, though the OP seems to have > got her answer from another post. [quoted text clipped - 38 lines] > > > > - Show quoted text - Well why don't you start by looking at your parentheses and ensure that you've got matching pairs in appropriate places for the syntax of the SUMPRODUCT function? -- David Biddulph > thanks guys, i've gotten the correct formula based on the below. now, > here's [quoted text clipped - 52 lines] >> > >> > - Show quoted text - thanks so much guys!! > Well why don't you start by looking at your parentheses and ensure that > you've got matching pairs in appropriate places for the syntax of the [quoted text clipped - 58 lines] > >> > > >> > - Show quoted text - See my reply to your earlier request on this in this same thread - does that do it for you? Pete > thanks guys, i've gotten the correct formula based on the below. now, here's > a modification: when i add another variable in the beginning, i can't get [quoted text clipped - 4 lines] > slight modification, i want the sum the qty of what's in those rows based on > the dates. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.