Sum values between dates

[Scott Frickman]

In col C8:C200 I have start dates entered by date and time. In col J8:J200
I have end dates entered by date and time. In col N8:N200 I have values for
each transcation.

What I would like to do is in cell C1 enter a start date. In cell C2 enter
a end date. In cell C3 have a formula that based on C1 and C2 find the
dates that are equal to, or between the start and end dates and based on
those match sum the values in col N

 

[Don Guillett]

try
=sumproduct((c8:c200>c1)*(j8:j200<c2)*n8:n200)

 

[Scott Frickman]

Don
Thanks for the quick reply. However the formula doesn't seem to work. It
returns a value but it is not correct. I am sure it must have something to
do with the way the dates are recorded. When I create a simple sheet and
enter dates, your formula works fine. So that is why I know it must be the
date/time. All the data is downloaded so I don't enter any of the data
directly.

Here is what the date/time info looks like in a cell in Col C:

6/6/2005 12:23:00 AM

I know the format is ok, because I can go into format and change the date
display.

Any further ideas?

Frick

 

[Aladin Akyurek]

=SUMPRODUCT((INT($C$8:$C$100)>=C1)+0,(INT($C$8:$C$100)<=C2)+0,$J$8:$J$200)

In col C8:C200 I have start dates entered by date and time. In col J8:J200
I have end dates entered by date and time. In col N8:N200 I have values for
each transcation.

What I would like to do is in cell C1 enter a start date. In cell C2 enter
a end date. In cell C3 have a formula that based on C1 and C2 find the
dates that are equal to, or between the start and end dates and based on
those match sum the values in col N

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.