Countif cell fall within date range

[Mr Peabody]

I have a spreadsheet with a number of dates
in a column

I would like to create a table which counts how many cells fall within each month

I ve tried countif but can only use one criteria - i need something like countif cells are >= 01oct02 and <=31oct02

can u help

 

[B_2]

=SUMPRODUCT((B3:B100>=DATE(2002,1,1))*(B3:B100<=DATE(2002,1,31)))


just substitue your dates for mine in the order shown
This message was edited by B_2 on 2002-10-16 11:55

 

[Mark W.]

Formula revised below...
This message was edited by Mark W. on 2002-10-16 13:13

 

[Aladin Akyurek]

A bit nicer maybe...

Book3
	A	B	C	D
1	Dates			2002
2	8/7/2002		Jan	0
3	8/12/2002		Feb	0
4	8/17/2002		Mar	0
5	8/22/2002		Apr	0
6	8/27/2002		May	0
7	9/1/2002		Jun	0
8	9/6/2002		Jul	0
9	9/11/2002		Aug	5
10	9/16/2002		Sep	6
11	9/21/2002		Oct	3
12	9/26/2002		Nov	0
13	10/1/2002		Dec	0
14	10/6/2002
15	10/11/2002
16
Sheet1

The formula in D2 is...

=SUMPRODUCT((MONTH($A$2:$A$15)=MONTH(C2&0))*(YEAR($A$2:$A$15)=$D$1))

 

[inarbeth]

Mark can you double check your formula - it always gives the answer 4.

 

[IML]

If you want to do this for every month, you could also consider a pivot table.
Put your date as you column heading and count of date as your data. Then right click on the date in the pivot table, select group and show detail, group, and select Months.

 

[Mark W.]

Oops!! You're right! Thanks, inarbeth. The formula should be...

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date		3
2	10/1/2002
3	10/24/2002
4	11/30/2002
5	10/15/2002
6
7
8
9
Sheet6

Note: This is an array formula which 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. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-10-16 13:14

 

[IML]

You could one of these two arrays
=COUNT(IF(MONTH(A2:A5)=10,A2:A5,""))
=SUM(IF(MONTH(A2:A5)=10,1,0))

or even
=COUNTIF(A2:A5,">"&DATE(YEAR(NOW()),10,0))-COUNTIF(A2:A5,">"&DATE(YEAR(NOW()),10+1,0))
This message was edited by IML on 2002-10-16 12:47

 

[Mr Peabody]

ALADIN

I've worked with your idea and it's fixed it

thanks for your help guys