SUMIF, SUMPRODUCT, DSUM, ?? Please help

[Ioweyouone]

To All:
I would like to sum a column of values (column C, named range “ONRENT”). Column A contains my starting date (range named “OUT”). Column B contains my ending date (range named “IN”). Cell E2 will contain a random date. Cell F2 will contain the formula. The formula should return a total sum of all cells in “ONRENT” where the date in E2 falls BETWEEN the two dates listed in the ranges “OUT” and “IN”. And finally, after all the values in “ONRENT” that meet the criteria are totaled, that sum must be subtracted from the value in D2. The formula must be able to return a negative number. I hope to keep the formula as basic as possible. It needs to be entered into 3000 cells per each sheet of a 12 sheet workbook. I really need some help.
<TABLE style="MARGIN: auto auto auto 4.65pt; WIDTH: 5.75in; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=690><TBODY><TR style="HEIGHT: 13.2pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #bfbfbf; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

OUT<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #bfbfbf; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

IN<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #bfbfbf; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

ONRENT<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #bfbfbf; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

INVENTORY<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #bfbfbf; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

DAY<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #bfbfbf; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

AVAILABLE<o></o>​

</TD></TR><TR style="HEIGHT: 13.2pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

1/1/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

6/1/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

10<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

100<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

9/10/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

89<o></o>​

</TD></TR><TR style="HEIGHT: 13.2pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

7/1/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

8/1/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

5<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap></TD></TR><TR style="HEIGHT: 13.2pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

9/1/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

10/1/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

5<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap></TD></TR><TR style="HEIGHT: 13.2pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

11/1/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

12/1/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

10<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap></TD></TR><TR style="HEIGHT: 13.2pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

9/2/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

9/10/1990<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=115 noWrap>

6<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap>

<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 69pt; PADDING-RIGHT: 5.4pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=115 noWrap></TD></TR></TBODY></TABLE>

 

[Aladin Akyurek]

Try...

F2:

=$D2-SUMPRODUCT(--(OUT>=E2),--(E2<=IN),ONRENT)

 

[Ioweyouone]

Try...

F2:

=$D2-SUMPRODUCT(--(OUT>=E2),--(E2<=IN),ONRENT)

Thank you...
It did work but I had to transpose OUT>=E2 to E2>=Out.
Can you please explain the "--" operator. What does it do?

 

[Aladin Akyurek]

Thank you...
It did work but I had to transpose OUT>=E2 to E2>=Out.

You are welcome.

Can you please explain the "--" operator. What does it do?

SumProduct applies to numbers, not to logical values or text...

A term like

OUT <= E2

evaluates to a set of TRUE/FALSE's. Given the fact that TRUE is represented by 1 and FALSE by 0. The double minus (just like +0)

--(OUT <= E2)

forces TRUE's value to 1's and FALSE's value to 0's, yielding a set of 1/0's SumProduct can use.

For more...

http://www.mrexcel.com/forum/showthread.php?t=70547
http://www.mrexcel.com/forum/showthread.php?t=202204
http://www.mrexcel.com/forum/showthread.php?t=128907
http://www.mrexcel.com/forum/showthread.php?p=1671486

 

[Ioweyouone]

Thanks again Aladin,
I feel fortunate to have this forum available.

 

[Aladin Akyurek]

Thanks again Aladin,
I feel fortunate to have this forum available.

You are welcome. Thanks for providing feedback.