counting months

[eubanj]

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]

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

 

[Jacob Skaria]

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

 

[T. Valko]

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)

 

[JoeU2004]

=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 -----