1. ## CountIF

I am using excel 97 and I want to count the number of entries between a date range. I have entered =countif(a1:a40,">09/01/01 and <11/30/01"). I only get zeros. Can anyone help??

Thanks,
Deborah

2. ## Re: CountIF

I don't think you can do more than one parameter in countif. I tried this out and it seems to work:

=COUNTA(A1:A40)-(COUNTIF(A1:A40,">11/29/01")+COUNTIF(A1:A40,"<9/2/01"))

This says to:
count all the non-blank cells,
then subtract the sum of
# of cells greater with values greater than Nov 29 and
# of cells less with values less than Sept 1

assuming that you want to count any dates from 9/2 to 11/29 only. If you really want to count Sept, Oct & Nov days only, then it should read >11/30 and <9/1.

I hope this helps!

Eve

3. ## Re: CountIF

If you'd like to see a great discussion of date intervals, go to <A target="_blank" HREF=http://www.cpearson.com/excel/DateIntervals.htm>Chip Pearson's web site</A>.

4. ## Re: CountIF

Hi
Does this work ?

=countif(Range,>=MinDate)-countif(range,>MaxDate)

Cheers
Geof

5. ## Re: CountIF

Hi Deborah
Try this userdefined function

Function CountDateBetween(inRange, minDate As Date, maxDate As Date)
Dim MinCtr, MaxCtr As Integer 'counter for dates exceeding limits
MinCtr = Application.CountIf(inRange, ">=" & minDate)
MaxCtr = Application.CountIf(inRange, ">" & maxDate)
CountDateBetween = MinCtr - MaxCtr
End Function

Copy & paste into the personal workbook macros. This workbook is normally hidden.
You will be then able to invoke thefunction using the function wizard (fx button) from the user defined category.

Cheers
Geof

6. ## Re: CountIF

Whenever I received your post through my email it had < before the last date range. I can follow the logic up until I hit this. I know the & is a concatenation character but what are the others? The formula works great!!

Thanks,
Deborah

7. ## Re: CountIF

The following array formula should give you what you are asking for:

<pre>=SUM((A1:A40 > DATEVALUE("9/1/01")) * (A1:A40 < DATEVALUE("11/30/01")))
</pre>

That is an array formula, so you must hold down the Ctrl and Shift keys when you press the Enter key to enter the formula into the cell.

8. ## Re: CountIF

Here is the formula again before I try to explain:

<pre>=SUM((A1:A40 > DATEVALUE("9/1/01")) * (A1:A40 < DATEVALUE("11/30/01")))
</pre>

Since this is entered as an array formula, the (A1:A40 > DATEVALUE("9/1/01") part of the formula will compare the dates in cells A1 through A40 to the date 9/1/01 and create an array of forty ones or zeros. There will be a one in the array where the date in the corresponding cell is greater than 9/1/01, and a zero where it is less. The second part of the formula creates a second array with ones where the date is less than 11/30/01 and a zero where it is greater. The asterich (*) multiplies the those two arrays together to create an array where the elements are one if the date in the corresponding cell is between the dates and a zero if it is outside the range. The SUM adds uo the elements in this array which counts the dates in the range.

There is no & in the formula, so I don't know what you are asking there.

9. ## Re: CountIF

Legare, when Lounge messages are e-mailed, the less than "<" symbol gets converted to "& l t" and the greater than ">" symbol gets converted to "& g t ", without the spaces.

10. ## Re: CountIF

John: Thanks. Does that happen if the formula is inside pre tags? I had tried to prevent that by using pre tags, but had a typo which converted [ pre ] to [pre}. I don't use the email options, so I don't know what happens there.

11. ## Re: CountIF

Thanks for the clarification!!!! The formula works great and I appreciate all the post!

Thanks,
Deborah

12. ## Re: CountIF

I'm not certain, send a test to me with preserve tags and I'll check.

13. ## Re: CountIF

Gee, John, you could send a test to YOURSELF and check. Doh!

greater than<pre>></pre>

less than<pre><</pre>

asterisk<pre>*</pre>

equals<pre>=</pre>

number/pound/hash/sharp<pre>#</pre>

ampersand<pre>&</pre>

caret<pre>^</pre>

14. ## Re: CountIF

Here's what e-mail delivers (item in quotes edited to add spaces):
greater thanless than"& l t";asterisk*equals=number/pound/hash/sharp#ampersand&caret^

The greater than symbol disappears altogether, as does the "pre" spacing.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•