I need to add all the months that are greater or equal to 6/01/09 but less
than 9/30/09. All the dates are in one column.
Example: 6/01/09 = 1
7/30/09 = 1
8/01/09 = 1
total = 3
Thanks for your help with this.

=SUMPRODUCT(--(A2:A500=DATEVALUE("6/1/09")*(A2:A500<DATEVALUE("9/30/09"))))

"eubanj" wrote:

I need to add all the months that are greater or equal to 6/01/09 but less
than 9/30/09. All the dates are in one column.
Example: 6/01/09 = 1
7/30/09 = 1
8/01/09 = 1
total = 3
Thanks for your help with this.

"Sean Timmons" wrote:
=SUMPRODUCT(--(A2:A500=DATEVALUE("6/1/09")*(A2:A500<DATEVALUE("9/30/09"))))

Either:

=SUMPRODUCT(--(A2:A500=DATEVALUE("6/1/09")),--(A2:A500<DATEVALUE("9/30/09")))

Or:

=SUMPRODUCT((A2:A500=DATEVALUE("6/1/09")) * (A2:A500<DATEVALUE("9/30/09")))

First, you do not need "--" if you are going to combine conditional
expressions with some other arithmetic operator (e.g. "*").

Second, your formula does not work because of misplaced parentheses. Test
with 10/1/2009 in some cell in A2:A500.

PS: Although DATEVALUE should work for this OP, since he used that date
form himself, I would prefer DATE(2009,6,1), since that is independent of
Regional and Language control settings.


----- original message -----

"Sean Timmons" wrote in message
...
=SUMPRODUCT(--(A2:A500=DATEVALUE("6/1/09")*(A2:A500<DATEVALUE("9/30/09"))))

"eubanj" wrote:

I need to add all the months that are greater or equal to 6/01/09 but
less
than 9/30/09. All the dates are in one column.
Example: 6/01/09 = 1
7/30/09 = 1
8/01/09 = 1
total = 3
Thanks for your help with this.

With data arranged in this format; use the below formula in D1

Col A Col B Col C Col D
6/1/2009 6/1/2009 9/30/2009 =formula
7/30/2009
8/1/2009
--
--
--

=COUNTIF(A:A,"=" & B1)-COUNTIF(A:A,"=" & C1)


If this post helps click Yes
---------------
Jacob Skaria


"eubanj" wrote:

I need to add all the months that are greater or equal to 6/01/09 but less
than 9/30/09. All the dates are in one column.
Example: 6/01/09 = 1
7/30/09 = 1
8/01/09 = 1
total = 3
Thanks for your help with this.

greater or equal to 6/01/09 but less than 9/30/09

I assume you mean you want to count *dates* that fall withn a date range.

One way...

Use cells to hold the boundary dates...

A1 = start date
B1 = end date

=COUNTIF(X1:X100,"="&A1)-COUNTIF(X1:X100,"="&B1)

--
Biff
Microsoft Excel MVP


"eubanj" wrote in message
...
I need to add all the months that are greater or equal to 6/01/09 but less
than 9/30/09. All the dates are in one column.
Example: 6/01/09 = 1
7/30/09 = 1
8/01/09 = 1
total = 3
Thanks for your help with this.