Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if within date range
Ok i've read through a number of the sumproduct solutions to this type of
question but i'm still yet to find one that works in this case. I have a worksheet that contains data about product rentals. To simplify the example I have 3 columns as below for temporary fencing hire. One row is for each customer: Meters hired Hire Date Return Date 12 24/07/2007 7/08/2007 87 15/10/2007 19/10/2007 72 14/12/2007 17/12/2007 25.5 22/06/2007 22/12/2007 30 7/12/2007 1/02/2008 Now I want to set up a function that will tell me how many meters I had out on hire (total) in say November 2007. Thankyou in advance for any assistance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if within date range
how many meters I had out on hire (total) in say November 2007.
Assuming data as posted within A2:C6, with real dates in cols B & C then this would return the above: =SUMPRODUCT((TEXT(B2:B6,"mmmyy")="Nov07")*A2:A6) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "fordrules01" wrote: Ok i've read through a number of the sumproduct solutions to this type of question but i'm still yet to find one that works in this case. I have a worksheet that contains data about product rentals. To simplify the example I have 3 columns as below for temporary fencing hire. One row is for each customer: Meters hired Hire Date Return Date 12 24/07/2007 7/08/2007 87 15/10/2007 19/10/2007 72 14/12/2007 17/12/2007 25.5 22/06/2007 22/12/2007 30 7/12/2007 1/02/2008 Now I want to set up a function that will tell me how many meters I had out on hire (total) in say November 2007. Thank you in advance for any assistance! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if within date range
Max,
Thankyou for your effort but this simply tells me how many meters went out during the month (0) and not the sum of how many meters were on hire during that month (97.5) Any other suggestions? "Max" wrote: how many meters I had out on hire (total) in say November 2007. Assuming data as posted within A2:C6, with real dates in cols B & C then this would return the above: =SUMPRODUCT((TEXT(B2:B6,"mmmyy")="Nov07")*A2:A6) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "fordrules01" wrote: Ok i've read through a number of the sumproduct solutions to this type of question but i'm still yet to find one that works in this case. I have a worksheet that contains data about product rentals. To simplify the example I have 3 columns as below for temporary fencing hire. One row is for each customer: Meters hired Hire Date Return Date 12 24/07/2007 7/08/2007 87 15/10/2007 19/10/2007 72 14/12/2007 17/12/2007 25.5 22/06/2007 22/12/2007 30 7/12/2007 1/02/2008 Now I want to set up a function that will tell me how many meters I had out on hire (total) in say November 2007. Thank you in advance for any assistance! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if within date range
I think your result of 97.5 is incorrect. It should be 25.5.
Try this: =SUMPRODUCT(--(B2:B6<=DATE(2007,11,30)),--(C2:C6=DATE(2007,11,1)),A2:A6) Or, use cells to hold the date criteria: E2 = 1/11/2007 (d/m/y) F2 = 30/11/2007 (d/m/y) =SUMPRODUCT(--(B2:B6<=F2),--(C2:C6=E2),A2:A6) -- Biff Microsoft Excel MVP "fordrules01" wrote in message ... Max, Thankyou for your effort but this simply tells me how many meters went out during the month (0) and not the sum of how many meters were on hire during that month (97.5) Any other suggestions? "Max" wrote: how many meters I had out on hire (total) in say November 2007. Assuming data as posted within A2:C6, with real dates in cols B & C then this would return the above: =SUMPRODUCT((TEXT(B2:B6,"mmmyy")="Nov07")*A2:A6) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "fordrules01" wrote: Ok i've read through a number of the sumproduct solutions to this type of question but i'm still yet to find one that works in this case. I have a worksheet that contains data about product rentals. To simplify the example I have 3 columns as below for temporary fencing hire. One row is for each customer: Meters hired Hire Date Return Date 12 24/07/2007 7/08/2007 87 15/10/2007 19/10/2007 72 14/12/2007 17/12/2007 25.5 22/06/2007 22/12/2007 30 7/12/2007 1/02/2008 Now I want to set up a function that will tell me how many meters I had out on hire (total) in say November 2007. Thank you in advance for any assistance! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if within date range
My apologies. I mis-read it earlier.
Biff's response should take care of it. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum if within date range
Google says you have 5 replies,
but I see none. So here is yet another method: http://www.freefilehosting.net/download/3dk4e |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
How do I enter a date range ie -7 from current date in MS QUERY | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |