# Countif cell fall within date range

#### Mr Peabody

##### New Member
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

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### B_2

##### Board Regular
=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.

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

##### MrExcel MVP
A bit nicer maybe...
Book3
ABCD
1Dates2002
28/7/2002Jan0
38/12/2002Feb0
48/17/2002Mar0
58/22/2002Apr0
68/27/2002May0
79/1/2002Jun0
89/6/2002Jul0
99/11/2002Aug5
109/16/2002Sep6
119/21/2002Oct3
129/26/2002Nov0
1310/1/2002Dec0
1410/6/2002
1510/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

##### Well-known Member
Mark can you double check your formula - it always gives the answer 4.

#### IML

##### MrExcel MVP
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.

##### MrExcel MVP
Oops!! You're right! Thanks, inarbeth. The formula should be...
Book1
ABCDEFGHIJK
1Date3
210/1/2002
310/24/2002
411/30/2002
510/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

##### MrExcel MVP
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

##### New Member

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

Replies
3
Views
125
Replies
4
Views
266
Replies
2
Views
217
Replies
5
Views
204
Replies
1
Views
165

1,182,135
Messages
5,933,851
Members
436,915
Latest member
Cygne volant

### 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.

### Which adblocker are you using?

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

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