countif using dates

rferley

New Member
Joined
Apr 18, 2002
Messages
3
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?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
GOTCHA! I WAS TRUELY HOPING TO COUNT, BOTH BY MONTH, AND BY THE DATE OF THAT RESPECTIVE MONTH. AHH WELL SUCH AS LIFE.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top