Sum if within date range

[fordrules01]

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!

 

[Max]

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)

 

[fordrules01]

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?

 

[T. Valko]

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)

 

[Max]

My apologies. I mis-read it earlier.
Biff's response should take care of it.

---

 

[Herbert Seidenberg]

Google says you have 5 replies,
but I see none.
So here is yet another method:
http://www.freefilehosting.net/download/3dk4e