CountIF

Topic

New Reply

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

Reply | Quote

Replies

If you’d like to see a great discussion of date intervals, go to Chip Pearson’s web site.

Reply | Quote

Hi
Does this work ?

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

Cheers
Geof

Reply | Quote

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

Reply | Quote

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

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

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.

Reply | Quote

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

Reply | Quote

Here is the formula again before I try to explain:

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

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.

Reply | Quote

Legare, when Lounge messages are e-mailed, the less than “” symbol gets converted to “& g t “, without the spaces.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

greater than

>

less than

<

asterisk

*

equals

=

number/pound/hash/sharp

#

ampersand

&

caret

^

Reply | Quote

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.

Reply | Quote

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

Thanks,
Deborah

Reply | Quote