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!

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!

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!

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!

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
---

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