# 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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### 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
2
Views
150
Replies
4
Views
222
Replies
4
Views
190
Replies
2
Views
493
Replies
1
Views
344

1,148,277
Messages
5,745,816
Members
423,980
Latest member
zimza

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