Results 1 to 14 of 14
Thread: CountIF

20010511, 12:48 #1
 Join Date
 Feb 2001
 Location
 South Carolina, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20010511, 17:05 #2Eve BGuest
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 nonblank 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

20010511, 23:08 #3
 Join Date
 Dec 2000
 Location
 Tacoma, Washington, USA
 Posts
 68
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>.
<IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

20010512, 20:42 #4
 Join Date
 Apr 2001
 Location
 Levin, ManawatuWanganui, New Zealand
 Posts
 324
 Thanks
 9
 Thanked 28 Times in 26 Posts
Re: CountIF
Hi
Does this work ?
=countif(Range,>=MinDate)countif(range,>MaxDate)
Cheers
Geof

20010512, 22:22 #5
 Join Date
 Apr 2001
 Location
 Levin, ManawatuWanganui, New Zealand
 Posts
 324
 Thanks
 9
 Thanked 28 Times in 26 Posts
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

20010514, 14:35 #6
 Join Date
 Feb 2001
 Location
 South Carolina, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20010514, 17:12 #7
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.Legare Coleman

20010514, 17:24 #8
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.Legare Coleman

20010514, 17:38 #9
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: CountIF
Legare, when Lounge messages are emailed, the less than "<" symbol gets converted to "& l t" and the greater than ">" symbol gets converted to "& g t ", without the spaces.
John ... I float in liquid gardens
UTC 7ąDS

20010514, 17:46 #10
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
Legare Coleman

20010514, 17:47 #11
 Join Date
 Feb 2001
 Location
 South Carolina, USA
 Posts
 66
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: CountIF
Thanks for the clarification!!!! The formula works great and I appreciate all the post!
Thanks,
Deborah

20010514, 19:27 #12
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: CountIF
I'm not certain, send a test to me with preserve tags and I'll check.
John ... I float in liquid gardens
UTC 7ąDS

20010514, 19:39 #13
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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>
John ... I float in liquid gardens
UTC 7ąDS

20010514, 19:43 #14
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: CountIF
Here's what email 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.John ... I float in liquid gardens
UTC 7ąDS