countif using dates

[rferley]

I have a spreadsheet that has dates in one column formatted as dd/mm/yyyy and I'm trying to count the number of jan, feb, etc. occurances in this spreadsheet. I've used the countif function as so: =COUNTIF(F2:F74;=>"2/1/2002")+COUNTIF(F2:F74;=<"2/28/2002") and get an error result for the formula. I've also tried the sumif function and can't seem to get the right configuration for the formula. Any ideas/concerns/further suggestions would be GREATLY appreciated?

[Aladin Akyurek]

On 2002-04-19 11:34, rferley wrote:
I have a spreadsheet that has dates in one column formatted as dd/mm/yyyy and I'm trying to count the number of jan, feb, etc. occurances in this spreadsheet. I've used the countif function as so: =COUNTIF(F2:F74;=>"2/1/2002")+COUNTIF(F2:F74;=<"2/28/2002") and get an error result for the formula. I've also tried the sumif function and can't seem to get the right configuration for the formula. Any ideas/concerns/further suggestions would be GREATLY appreciated?

Make a list of month numbers, say in G from G2 on.

In H2 enter:

=SUMPRODUCT((MONTH($F$2:$F$74)=G2)+0)

and copy down as far as needed.



[ This Message was edited by: Aladin Akyurek on 2002-04-19 11:50 ]

[Dave Patton]

The following 2 alternatives work.
Note my system is configured to use dd/mm/yy
but not separators of ";".

=COUNTIF(F2:F7,">=1/1/2002")-COUNTIF(F2:F74,">=31/1/2002")

with criteria in D1 and D2

=COUNTIF(F2:F7,">="&D1)-COUNTIF(F2:F74,">="&D2)

[Mark W.]

Your errors are caused in part by invalid relational operators...

Use >= instead of =>
Use <= instead of =<

Also, the operator should be included in text string used as COUNTIF's 2nd argument.

[ This Message was edited by: Mark W. on 2002-04-19 11:51 ]

[Yogi Anand]

On 2002-04-19 11:34, rferley wrote:
I have a spreadsheet that has dates in one column formatted as dd/mm/yyyy and I'm trying to count the number of jan, feb, etc. occurances in this spreadsheet. I've used the countif function as so: =COUNTIF(F2:F74;=>"2/1/2002")+COUNTIF(F2:F74;=<"2/28/2002") and get an error result for the formula. I've also tried the sumif function and can't seem to get the right configuration for the formula. Any ideas/concerns/further suggestions would be GREATLY appreciated?

use
=COUNTIF(F2:F74,">=2/1/2002")

[rferley]

Thanks for all the suggestions, here's where I'm at =COUNTIF(F2:F74,">=01/02/02")+COUNTIF(F2:F74,"<=28/02/02") the result is a number which I know to be untrue. It returns a value of 57 and I know there were only 13 occurances in Feb. The same holds true for March and April, the formula returns a number, just not an accurate one.

[Mark W.]

On 2002-04-19 13:57, rferley wrote:
Thanks for all the suggestions, here's where I'm at =COUNTIF(F2:F74,">=01/02/02")+COUNTIF(F2:F74,"<=28/02/02") the result is a number which I know to be untrue. It returns a value of 57 and I know there were only 13 occurances in Feb. The same holds true for March and April, the formula returns a number, just not an accurate one.

That because your formula is syntactically correct, but perhaps semantically wrong.

I'm guessing that you think your formula is counting the entries between these dates (the intersection), but actually your formula is counting the union -- all of your dates.

This array formula would do the trick...

{=COUNT(IF(MONTH(F2:F74)=2,1))}


Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

[ This Message was edited by: Mark W. on 2002-04-19 14:09 ]

[Yogi Anand]

On 2002-04-19 13:57, rferley wrote:
Thanks for all the suggestions, here's where I'm at =COUNTIF(F2:F74,">=01/02/02")+COUNTIF(F2:F74,"<=28/02/02") the result is a number which I know to be untrue. It returns a value of 57 and I know there were only 13 occurances in Feb. The same holds true for March and April, the formula returns a number, just not an accurate one.

Hi rferley:
Depends on what you are trying to compute. The first expression computes the number of all transactions date equal to or greater than 1/2/2002 (which by the way includes all transaction dates covered by your second expression). Are you sure you want to add the two expressions? perhaps you want to subtract them -- we will know wwhether the two expressions should be added or subtracted if you provide a clear statement.

HTH

[Aladin Akyurek]

On 2002-04-19 13:57, rferley wrote:
Thanks for all the suggestions, here's where I'm at =COUNTIF(F2:F74,">=01/02/02")+COUNTIF(F2:F74,"<=28/02/02") the result is a number which I know to be untrue. It returns a value of 57 and I know there were only 13 occurances in Feb. The same holds true for March and April, the formula returns a number, just not an accurate one.

Since you want to do a count per month, it's better not to plug a constant in the condition/crit argument of COUNTIF (or other functions).

Moreover, your formula should read:

=COUNTIF(F2:F74,">=01/02/02")-COUNTIF(F2:F74,">28/02/02")

Give also a try to what I already suggested.

Another alternative is:

In G2 enter and copy down:

=MONTH(F2)

Then use a straight COUNTIF, e.g., for February:

=COUNTIF($G$2:$G$74,2)

Aladin

[ This Message was edited by: aladin akyurek on 2002-04-20 02:00 ]

[rferley]

GOTCHA! I WAS TRUELY HOPING TO COUNT, BOTH BY MONTH, AND BY THE DATE OF THAT RESPECTIVE MONTH. AHH WELL SUCH AS LIFE.