Sumif Date is Between two Dates

[exceler1]

Hi!
I want to make a report for an equipment rental company. I have a list of SHIPPED_DATE and RETURN_DATE and the value of equipment.

I have a column of dates (see column I). I want to know at any given date the amount of equipment that was rented out.I need to now on, say 1/15/11 the total equipment out. So 1/15/2011 is >= than SHIPPED_DATE and <= than RETURN_DATE.

Thanks in advance!!

[Roger Govier]

Hi

Create a table of your data, as then the range will expand automatically as you add more data.

Place cursor in any cell on sheet3 >insert tab>table>my data had headers

Then with thee date you are testing in I1 enter in I2
=SUMPRODUCT((Table1[SHIPPED_DATE]<=$I$1)*(Table1[RETURN_DATE]<=$I$1))

[exceler1]

Thanks -
But I want to know the sum of equipment out on that day (column C)

[daddylonglegs]

Perhaps try SUMIFS

=SUMIFS(C:C,D:D,">="&I1,E:E,"<="&I1)

[Roger Govier]

Thanks -
But I want to know the sum of equipment out on that day (column C)

Hi
Apologies, I thought you wanted a count.

If you want the values then
=SUMPRODUCT((Table1[SHIPPED_DATE]<$I$1)*(Table1[RETURN_DATE]>$I$1)*Table1[ITEM_SUM])

Note this has been changed to BEFORE 15/02/2011 (or whatever date you type in I1)
and returned AFTER date in I1

If you want to INCLUDE the I1 day then amend formula to
=SUMPRODUCT((Table1[SHIPPED_DATE]<=$I$1)*(Table1[RETURN_DATE]>=$I$1)*Table1[ITEM_SUM])

[exceler1]

Thank you Roger!
That did it - works perfectly.

P.S. How doe you mark a thread "solved"?